SQL : Get yesterdays day with a certain timestamp-CodePudding

i am trying to get yesterdays date in SQL SERVER with a certain timestamp.

I know how to get yesterdays date in SQL without the timestamp using the following query :

Which gives me every startdate starting from '00:00:00':

SELECT se.startdate 
FROM sessions se
WHERE se.startdate >= DATEADD(day, -1, CAST(GETDATE() AS date))

Instead I want to get the yesterdays date with a certain timestamp. For this example every startdate starting from '06:00:00'(AM). Like shown below:

 SELECT se.startdate 
    FROM sessions se
    WHERE se.startdate >= '05-09-2022 06:00:00'

If I do it the way it was shown in the second example I would have to change the day manually, which would obviously repetitive.

Is there a way to combine the first example with second one so that we get always yesterdays date at '06:00:00' ? (OR ANY GIVEN TIME )

CodePudding user response：

For example 06:20:40 of the previous day:

select DATEADD(second, (6 * 60   20 ) * 60   40, cast(DATEADD(day, -1, cast(GETDATE() AS date)) as datetime)) t

CodePudding user response：

you can do it this way:

SELECT DATEADD(day, DATEDIFF(day, 1, GETDATE()), '06:00:00')

CodePudding user response：

Might as well add another solution, DATETIMEFROMPARTS:

DECLARE @Yesterday date = DATEADD(DAY, -1, GETDATE());
SELECT DATETIMEFROMPARTS(YEAR(@Yesterday),MONTH(@Yesterday),DAY(@Yesterday),6,0,0,0);

CodePudding user response：

If you want to combine DATEADD(hour, 6, ~) and DATEADD(day, -1, ~) use DATEADD(hour, -18, ~).

SELECT se.startdate 
FROM sessions se
WHERE se.startdate >= DATEADD(hour, -18, GETDATE() );