Applications

SQL Server: Removing Time from the Date Field for Comparisons

Consider this SQL SELECT statement:

SELECT 
    [TS].*
FROM 
    [dbo].[TimeSheet] [TS]
WHERE 
    ([TS].[EntryDate] = @ActivityStartDate)

Seems pretty straight forward, but you run into some issues when comparing dates with timestamps.

If @ActivityStartDate is passed into the query as 6/1/2014 12:00 AM, because a specific time was never specified, but the timesheet entries contain timestamps, then you run into some inconsistencies.

For example, if the Timesheet date is equal to 6/1/2014 10:00 AM, then the row will not be returned because the Timesheet date evaluates to LESS THAN the parameter.

You can use the DATEDIFF method to resolve this:

DATEDIFF(DAY, [TS].[EntryDate], @ActivityStartDate) = 0

Or you can simply remove the timestamp:

DATEADD(DD, DATEDIFF(DD, 0, [TS].[EntryDate]), 0) = @ActivityStartDate

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.