Query List of Datetimes for same date

Hi:

I have created a table that has a column of smalldatetimes. For any given day, there may be different records with the same date but with different times. I've created the query SELECT Event FROM Events where Start=@.Start and I input start as a selected date from the calendar control. Because of the different times, I do not get any matches. Could someone help me with a query that will get all of the events on the same day?

Regards,

Roger

Yep, dealing with the fact that SQL Server date values include the times can be a pain. The solution usually involves doing one of two things, either normalizing the date values to use the default time of 12:00 (midnight, or 0 seconds from midnight) or using the BETWEEN keyword in the WHERE clause of the query.

Probably the best resource for figuring out how to proceed is Itzik Ben-Gan's series of article for SQL Server Magazine about date/time values. I'm not sure whether you have to be a subscriber to read them, but the first in the series of five isDATETIME Calculations, part 1.

One way of doing the query would be like this, using one of Itzik's techniques to compare the table's DateTimeStart field to today:

SELECT * FROM [FAHCCalendar]
where DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101')
= DATEADD(day, DATEDIFF(day, '19000101', [DateTimeStart]), '19000101')

That is only one of several techniques he uses. To try this out in a more raw form to see what is happening, try this query:

SELECT DATEADD(day, DATEDIFF(day,
'19000101', GETDATE()),
'19000101');

Otherwise you can use the T-SQL date functions to extract the day, month, and year from both dates and construct a normalized dates to compare.

Make sense?

Don

|||

Perfect - Thanks Don!

Roger