Date query using Calendar Control in ASP.NET

strSQL = "SELECT * " & _
" FROM Tracker2 " & _
" WHERE Start_date >= '" & BeginDate.SelectedDate & "' AND Ticket_type='" & ddlTicketType.SelectedValue() & "' ORDER By Start_date"

This query works to retrieve all records 'from' the date I choose in my calendar control. I would like to display for ONLY the date I choose on my calendar control, but when I change my query to

" WHERE Start_date = '" & BeginDate.SelectedDate & "'

it doesn't diaplay anything?? Why?

There may be an issue with your database field. what data type are you using in the database??? DateTime or SmallDateTime? how is it storing the values??? If it is storing the date and time in the database and you are only passing a date from the calendar, then you won't get any records returned, because the time isn't being passed.

If the database has this
01-22-2003 2:11:41 PM

And you are passing this from you calendar
01-22-2003

There won't be any records returned, because every record in the database has a time associated with it.

I have it as datatime on my SQL server like this 01-22-2003 2:11:41 PM.

But a range of date query works :

strSQL = "SELECT *" & _
" FROM Tracker2 " & _
" WHERE Start_date >= '" & BeginDate.SelectedDate & "' AND Start_date <= '" & EndDate.SelectedDate & "' AND Ticket_type='" & ddlTicketType.SelectedValue() & "' ORDER By Start_date"

where BeginDate and EndDate are 2 calendar controls.

I would like to use only 1 Calendar control and want to change the WHERE part of the query to " WHERE Start_date = '" & BeginDate.SelectedDate & "' -- so that I can see the records entered for 1 day at a time

Also, when I enter the EndDate to be 'today's date' it displays only the records till yesterday...it DOES NOT show the records added today??? Is it bcos of the datetime format..if so how do I rectify it??

The reason the date range works is because when you select the start date in the calendar (01-22-2003) and the end date (01-23-2003) and pass it to the database, it is appending the time onto it like this (start date = 01-22-2003 00:00:00 AM...end date = 01-23-2003 00:00:00 AM). Therefore it will return rows where the start date is greater 0r equal to 01-22-2003 00:00:00 AM and the end date is less than or equal to 01-23-2003 00:00:00 AM.

but unless you have a row with the start date = 01-22-2003 00:00:00 AM, it won't find a record.

If you just pass in a date, it will default the time to midnight

so passing in 01-22-2003 and 01-23-2003 will only return records that were entered in between midnight on 01-22 and midnight on 01-23 (a 24 hour period)

the best way to do it would be to not have the time on in the date...change the datatype to smalldatetime and pass your dates without the time like this


or change the data type of the date fields to varchar(10) or text 10 (Access)...The time part is what is causing the problem when you are searching for records that equal a specific date.

<del>