Get date Minus 1 day but at exacty 4 AM

Question

I have the following sql server WHERE clause:

WHERE (DateCreated >= CONVERT(datetime, GETDATE(), 111) - 1)

This gets the date (where today is 2015-06-09) 2015-06-08. I need to add a time to this as well like 2015-06-08 04:00:00 in 24H format. the time will always be the same bat every time the SQL command is executed, it should only be from yesterday at 4 AM to the current date and time.

how can this be achieved?

Answer 1

Try this:

WHERE DateCreated >= dateadd(d, datediff(d, 1, getdate()), '04:00')

Answer 2

I think you are looking for:

WHERE (DateCreated >= DATEADD(HOUR, 4, 
                        CONVERT(datetime, 
                            DATEADD(DAY, -1, CONVERT(date, GETDATE()) )
                               )
                              )
       )

Converting directly to DATE will take away the hassle of taking care of the hour part. After that, doing a DATEADD with -1 will take you 1 day ago.

After this step, simply convert it back to datetime to create a timestamp part to your date, which is defaulted to 00:00:00.000.

And in the end, simply add 4 hours to this start date, which will always give you 4:00 AM.