select 1 days worth of data every day

Question

Post reply

select 1 days worth of data every day

maui

SSC Veteran

Points: 250
More actions
September 29, 2008 at 9:53 am

#213330

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

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

maui SSC Veteran Points: 250 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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