select 1 days worth of data every day

  • I have a SSIS Package that I will be running daily, I want to select 1 full days worth of data each day. I've worked it out by hard coding the time & date as you can see from the following code:

    select *

    from table

    where purchase_date > '2008-09-29 00:00:00'

    and purchase_date < '2008-09-29 23:59:59'

    How can I make this date dynamic and keep the time the same? Is there a better way of what I am doing?

    Thanks

  • This might help:

    [font="Courier New"]DECLARE @Today DATETIME

    SET @Today = DATEADD(dd,DATEDIFF(dd, 0, GETDATE()), 0) -- today with no time component

    -- Sanity check:

    SELECT @Today, DATEADD(dd, 1, @Today) AS Tomorrow_1, DATEADD(dd,1+DATEDIFF(dd, 0, GETDATE()), 0) AS Tomorrow_2

    -- using variables:

    SELECT *

    FROM TABLE

    WHERE purchase_date >= @Today

    AND purchase_date < DATEADD(dd, 1, @Today)

    -- or without variables:

    SELECT *

    FROM TABLE

    WHERE purchase_date >= DATEADD(dd,DATEDIFF(dd, 0, GETDATE()), 0)

    AND purchase_date < DATEADD(dd,1+DATEDIFF(dd, 0, GETDATE()), 0)

    [/font]

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Chris,

    Thanks for the quick the response, I chose your example without the variables, changed the table name and hey presto..!!

    Works a treat, exactly what I was after.

    Thank you.

    maui

  • You're welcome maui, many thanks for the feedback.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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