DateTime in Calendar Extender

Question

Hi guys, I am using calendarextender control in asp.net.It displays only date ,not time.But while inserting to database ,time is inserted automatically.But while retrieving the data from database using date in where condition ,no correponding records displays.What modification in storedprocedure i can do to resolve this problem.

Answer 1

The problem you are most likely encountering is that if you are looking for a date equal to 3/11/2009 you'll probably never find it, because of the time stamp. A few methods you could use to change the strored procedure would be to consider the range for the date:

Using the Between Statement will give you an inclusive range

Select myname,mydate from sometable 
    where mydate between 3/10/2009 and 3/11/2009

Or

Using > and < to give you an exclusive range

Select myname,mydate from sometable 
    where mydate >3/10/2009 and mydate < 3/12/2009

Or

Leverage the DateAdd function to strip the time off the field. There are some good examples of this at the SQLTeam Blog

dateadd(dd,0, datediff(dd,0,myDate))

Or If you can change the Table itself and you are using SQL Server 2008 you could use the new SQL Server 2008 Date DataType, a date with no time component to deal with. There's a good blog over on MSDN blogs about the new Date and Time enhancements in SQL Server 2008.

Don't forget you could also look into the insert procedure(if applicable) and zero out the time.

Also when dealing with datetime make sure to factor in >= and <= so you don't miss any records.

Hope this gives you enough options to solve your problem.