How to code to find date and time

Question

How to code to find date and time

adonetok

SSCrazy Eights

Points: 8395
More actions
November 21, 2017 at 9:04 am

#402686

I need to run daily script to create a report.
How to code to find out data like below
select * from order where orderDateTime between ('getdate()-2 10:00 AM') and ('getdate()-1 5:00 PM')?
For example, today date in 2017-11-21
I need a report
between 2017-11-19 10:00 AM and 2017-11-20 5:00 PM
Tomorrow is 2017-11-22, I need a report
between 2017-11-20 10:00 AM and 2017-11-21 5:00 PM
I tried to use DATEADD but don't know how to use it for time.

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

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

Ken McKelvey SSCoach Points: 18861 More actions · Answer 1

WHERE orderDateTime BETWEEN DATEADD(hour, 10, DATEADD(day, DATEDIFF(day, 2, CURRENT_TIMESTAMP), 0)) AND DATEADD(hour, 17, DATEADD(day, DATEDIFF(day, 1, CURRENT_TIMESTAMP), 0))

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

adonetok - Tuesday, November 21, 2017 9:04 AM
I need to run daily script to create a report.
How to code to find out data like below
select * from order where orderDateTime between ('getdate()-2 10:00 AM') and ('getdate()-1 5:00 PM')?
For example, today date in 2017-11-21
I need a report
between 2017-11-19 10:00 AM and 2017-11-20 5:00 PM
Tomorrow is 2017-11-22, I need a report
between 2017-11-20 10:00 AM and 2017-11-21 5:00 PM
I tried to use DATEADD but don't know how to use it for time.

This should help:
DECLARE @testdate DATETIME;

SET @testdate = GETDATE();
SELECT @testdate, DATEADD(HOUR,10,DATEADD(DAY,DATEDIFF(DAY,0,@testdate) - 2, 0)), DATEADD(HOUR,17,DATEADD(DAY,DATEDIFF(DAY,0,@testdate) - 1, 0))