Converting GetDate() to current date with a specific time

[MrPogle]

I want to return the the date for the day before my query runs but at a specific time. The time is not midnight, so:

convert(varchar (19),getdate() and '08:00:00',101)

does not work. Instead I want to return the day before at 8am.

Thanks.

[azimmer]

I want to return the the date for the day before my query runs but at a specific time. The time is not midnight, so:

convert(varchar (19),getdate() and '08:00:00',101)

does not work. Instead I want to return the day before at 8am.

Thanks.

convert(datetime,left(convert(varchar, getdate(), 121),4+1+2+1+2) + ' 08:00')

[DaveStarns]

(dateadd(hour,8(dateadd(day,-1,convert(datetime,convert(date,getdate()),102)))))