Conrad Conrad - 1 month ago 9
SQL Question

How to create a time of 9pm today in SQL

I am trying to get a

smalldatetime
value of "9pm today" in a query. I thought I could use

DATEADD(HOUR, 21, CONVERT(date, GETDATE()))


but SQL Server doesn't like that - I get the error


The datepart hour is not supported by date function dateadd for data
type date.


Suggestions for a workaround?

Answer

Pretty simple, just cast date back to datetime after casting to date.

Thus you'll get current_date 00:00:00 and then add 21 hours:

select dateadd(hh, 21, cast(cast(getdate() as date) as datetime))