How to get current date between?

Question

How to get current date between?

krypto69

SSChampion

Points: 13591
More actions
January 14, 2015 at 3:17 pm

#313637

Hi,
I have a query that will go into an ssis package (eventually). The package will run every night at 3am. I need to capture the last 24 hours of by using something like:
SELECT worktype, changedate, woclass
where siteid = 'GTM' and woclass = 'WORKORDER' and istask = 0
[highlight=#ffff11]and changedate between '2015-01-13 03:00:00' and '2015-01-14 03:00:00'[/highlight]
I know I am not doing the between correctly to get the changedate between the last 24 hours. Is there a way to correct this so that I am only getting the change date that is between 3am today and 3am yesterday on any given day I happen to run this?

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

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

joshdbguy SSCertifiable Points: 7707 More actions · Answer 1

krypto69 (1/14/2015)
Hi,
I have a query that will go into an ssis package (eventually). The package will run every night at 3am. I need to capture the last 24 hours of by using something like:
SELECT worktype, changedate, woclass
where siteid = 'GTM' and woclass = 'WORKORDER' and istask = 0
[highlight=#ffff11]and changedate between '2015-01-13 03:00:00' and '2015-01-14 03:00:00'[/highlight]
I know I am not doing the between correctly to get the changedate between the last 24 hours.

just use something like this instead,

where datediff(dd, changedate,getdate())<=1

krypto69 SSChampion Points: 13591 More actions · Answer 2

krypto69

SSChampion

Points: 13591

January 14, 2015 at 3:27 pm

#1770277

Thanks Josh. Much appreciated.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 3

JoshDBGuy (1/14/2015)
krypto69 (1/14/2015)
Hi,
I have a query that will go into an ssis package (eventually). The package will run every night at 3am. I need to capture the last 24 hours of by using something like:
SELECT worktype, changedate, woclass
where siteid = 'GTM' and woclass = 'WORKORDER' and istask = 0
[highlight=#ffff11]and changedate between '2015-01-13 03:00:00' and '2015-01-14 03:00:00'[/highlight]
I know I am not doing the between correctly to get the changedate between the last 24 hours.
just use something like this instead,
where datediff(dd, changedate,getdate())<=1

Ah... be careful, now. If "ChangeDate" is the name of a column in a table, that will make a non-SARGABLE predicate. In plain English, that mean it will never be able to take full advantage of an index. Use something like the following, instead.

WHERE ChangeDate >= DATEADD(dd,-1,GETDATE())

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

joshdbguy SSCertifiable Points: 7707 More actions · Answer 4

Jeff Moden (1/14/2015)
JoshDBGuy (1/14/2015)
krypto69 (1/14/2015)
Hi,
I have a query that will go into an ssis package (eventually). The package will run every night at 3am. I need to capture the last 24 hours of by using something like:
SELECT worktype, changedate, woclass
where siteid = 'GTM' and woclass = 'WORKORDER' and istask = 0
[highlight=#ffff11]and changedate between '2015-01-13 03:00:00' and '2015-01-14 03:00:00'[/highlight]
I know I am not doing the between correctly to get the changedate between the last 24 hours.
just use something like this instead,
where datediff(dd, changedate,getdate())<=1
Ah... be careful, now. If "ChangeDate" is the name of a column in a table, that will make a non-SARGABLE predicate. In plain English, that mean it will never be able to take full advantage of an index. Use something like the following, instead.
WHERE ChangeDate >= DATEADD(dd,-1,GETDATE())

I didn't realize that it's non-SARGABLE. This is good information for me as well.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 5

Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 6

declare @1 datetime = '2015-01-13 00:00:00';

declare @2 datetime = '2015-01-14 03:00:00';

select datediff(dd,@1,@2) as days, datediff(hh,@1,@2) as hours

/* 1 27 */

Do you need 1 day or 24 hours?

krypto69 SSChampion Points: 13591 More actions · Answer 7

Thanks guys.

It may be okay in this instance - because the table is just being used as a dump table and gets truncated every run.

Thanks again everyone for your help..great info.