where clause need to pull datetime column for yesterday at 3:00 pm to current time

Question

where clause need to pull datetime column for yesterday at 3:00 pm to current time

Asha.Richardson-1129890

SSC-Addicted

Points: 451
More actions
March 10, 2010 at 11:24 am

#217518

I have a query, I need to build in a where clause based on a datetime field. The where clause needs to filter data for that datetime column based on Yesterdays date at 3:00 pm to current date/time. So I know I need to use between "something" and getdate(). I just don't know how to construct the "something". do I do something like this?
declare @yesterday datetime
Set @yesterday = getdate() -1
between @yesterday and getdate()
what I am not understanding is how do I say yesterday at 3:00 pm?
I hope this makes sense.
thank you!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

So you want to use dateadd to move your date to yesterday. Then if it needs to be 3pm, you need to do some casting. You will need to get just the date, and then add in your specific time (3:00pm or 15:00) and then concatenate and cast back to a date value

My suggestion is to build a SELECT first that just gets you the value in the variable, then then build the WHERE clause. I tend to do

WHERE date > @yesterday

and date < getdate()

Asha.Richardson-1129890 SSC-Addicted Points: 451 More actions · Answer 2

I should have said that I was pretty new to developing, so I kind of understand your terminology, but could you give me an example.

Asha.Richardson-1129890 SSC-Addicted Points: 451 More actions · Answer 3

Asha.Richardson-1129890

SSC-Addicted

Points: 451

March 10, 2010 at 11:43 am

#1131596

BTW, Thank you for responding!

LutzM SSC Guru Points: 107049 More actions · Answer 4

The following sample will add 15 hours to the day before today ( GETDATE()-1 ), normalized to the beginning of the day using a common dateadd/datediff routine.

DECLARE @yesterday DATETIME

SET @yesterday = DATEADD(hh,15,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))

SELECT @yesterday

I wouldn't use any varchar concatenation though...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 5

DECLARE @Yesterday datetime

SET @Yesterday = CONVERT(DATE,GETDATE() - 1)

SELECT DATEADD(HH,15,@YesterDay)

Converting oxygen into carbon dioxide, since 1955.

Asha.Richardson-1129890 SSC-Addicted Points: 451 More actions · Answer 6

This top one works - THANK YOU!

DECLARE @yesterday DATETIME

SET @yesterday = DATEADD(hh,15,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))

SELECT @yesterday

this one gives me an error: That says DATE is invalid.. not sure if I was suppose to put something else there instead of DATE anway, I have a solution so Thanks for helping!

DECLARE @Yesterday datetime

SET @Yesterday = CONVERT(DATE,GETDATE()- 1)

SELECT DATEADD(HH,15,@YesterDay)

You guys RAWK! all of you! Thanks for responding

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

The second is likely failing because you're not in SQL 2008 (or your DB is set to a lower compatibility level).

#2 doesn't work without the "simpler date" data type, so just use the one that works.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Paul White SSC Guru Points: 150467 More actions · Answer 8

The most compact way to find yesterday at 3pm is:

SELECT DATEADD(HOUR, -9, {fn CURRENT_DATE()});

Works on SQL Server 2005 and 2008.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi