Add a Specific Time to a Date Value

Question

I have a stored procedure that receives a date value like this:

@AsOf date

In the query I am using this date to filter a datetime field. I need to convert the date passed in to a datetime field with the time portion set to 09:00:00 PM so I can add a filter in the WHERE clause like this:

(DateCreated < @TimeAsOfNinePM)

I know this is easy, but it's just escaping me at the moment. I'm using SQL Server 2008.

Answer 1

Try this out:

DECLARE @TimeAsOfNinePM datetime 
DECLARE @AsOf date
SET @AsOf = '2010-10-01'
SET @TimeAsOfNinePM = dateadd(hh,21, cast(@AsOf as datetime)) 

PRINT @TimeAsOfNinePM

The trick is you need to convert the date datatype to a datetime datatype to add hours to it.