Get date from midnight to midnight

  • I need to pull previous day data from midnight to midnight . Meaning 7/25 12:00 am to 7/25 11:59pm.

    how to get this?

  • komal145 (7/25/2016)


    I need to pull previous day data from midnight to midnight . Meaning 7/25 12:00 am to 7/25 11:59pm.

    how to get this?

    ...WHERE DateCol >= '20160725' and DateCol < '20160726'

    For a dynamic 'Previous Day', use

    DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 1, 0)

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Alternatively, you could cast your DATETIME data as DATE and compare those.

    WHERE CAST(DateCol AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)

    Since CASTing from DATETIME to DATE is still SARGable, this should perform fairly well.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • komal145 (7/25/2016)


    I need to pull previous day data from midnight to midnight . Meaning 7/25 12:00 am to 7/25 11:59pm.

    how to get this?

    Quick question, what data type is your date column?

    😎

  • drew.allen (7/25/2016)


    Alternatively, you could cast your DATETIME data as DATE and compare those.

    WHERE CAST(DateCol AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)

    Since CASTing from DATETIME to DATE is still SARGable, this should perform fairly well.

    Drew

    Reminder: don't use this on other data types as the queries might become non-SARGable.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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