Query Yesterday's Date

Question

Post reply

Query Yesterday's Date

drodriguez-762178

SSCommitted

Points: 1795
More actions
April 7, 2009 at 5:25 pm

#130452

Is there any easy way to automatically put a to and from date in a where clause? This is what I have so far. I thought of doing a dateadd minus one day on GetDate(), but I thought there might be a better way to do it.
WHERE dtStartTimeStamp >= '2009-04-06 00:00:00.000' AND dtStartTimeStamp <= '2009-04-06 23:59:59.000'
Thanks,Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

This is a better way to code date range queries:

WHERE

-- Greater than or equal to 00:00:00.000 yesterday

dtStartTimeStamp >= '2009-04-06 00:00:00.000' AND

-- Before 00:00:00.000 today

dtStartTimeStamp < '2009-04-07 00:00:00.000'

--Using getdate() to find date range for yesterday

WHERE

-- Greater than or equal to 00:00:00.000 yesterday

dtStartTimeStamp >= dateadd(dd,datediff(dd,0,getdate())-1,0) and

-- Before 00:00:00.000 today

dtStartTimeStamp < dateadd(dd,datediff(dd,0,getdate()),0)

drodriguez-762178 SSCommitted Points: 1795 More actions · Answer 2

Thanks! This is what I came up with.

Let me know what you think.

WHERE dtStartTimeStamp >= CONVERT(varchar(10),DATEADD(day,-1, GETDATE()),20) + ' 00:00:00.000' AND dtStartTimeStamp <= CONVERT(varchar(10),DATEADD(day,-1, GETDATE()),20) + ' 23:59:59.000'

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

drodriguez (4/7/2009)
Thanks! This is what I came up with.
Let me know what you think.
WHERE dtStartTimeStamp >= CONVERT(varchar(10),DATEADD(day,-1, GETDATE()),20) + ' 00:00:00.000' AND dtStartTimeStamp <= CONVERT(varchar(10),DATEADD(day,-1, GETDATE()),20) + ' 23:59:59.000'

I think this is bad for two reasons.

1. I already posted the most efficient way to find the date range using DATEADD/DATEDIFF

2. You query has a logical error, because it would miss any time after 23:59:59.000, for example 23:59:59.357.

It is alway better to query a date range this way where StartDatetime is the first point in time to include and EndDatetime is the first point in time to exclude.

where MyDate >= StartDatetime and MyDate < EndDatetime

drodriguez-762178 SSCommitted Points: 1795 More actions · Answer 4

Thanks for your help. I knew there was a better way to do it.

Thanks again:-),

Dave