Post reply

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

  • 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!

  • March 10, 2010 at 11:40 am

    #1131593

    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()

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • March 10, 2010 at 11:43 am

    #1131595

    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.

  • March 10, 2010 at 11:43 am

    #1131596

    BTW, Thank you for responding!

  • March 10, 2010 at 11:46 am

    #1131597

    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]

  • March 10, 2010 at 11:49 am

    #1131598

    DECLARE @Yesterday datetime

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

    SELECT DATEADD(HH,15,@YesterDay)

    Converting oxygen into carbon dioxide, since 1955.

  • March 10, 2010 at 11:54 am

    #1131602

    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

  • March 10, 2010 at 2:45 pm

    #1131661

    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?

  • March 11, 2010 at 5:20 am

    #1131942

    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

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

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