Construct DateTime using today's date at a specific time

8
votes

I'd like to get 4:30 PM of the current day. Hard-coding this way doesn't work:

SELECT '07242012 16:30:00.000'

This is proving to be more difficult than I thought it would be. How do I approach this?

4
sql-serversql-server-2008-r2
In the future, it can be very helpful if you meantion what version of SQL Server you're using. - Aaron Bertrand
Ah, will update the question to reflect that. - John Zumbrum
I changed the edit to indicate version via a tag. Easier than trying to find version information buried in the question. - Aaron Bertrand

4 Answers

30
votes

SQL Server 2000 / 2005:

SELECT DATEADD(MINUTE, 30, DATEADD(HOUR, 16, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)));

-- or

SELECT DATEADD(MINUTE, (16*60) + 30, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))

-- or

SELECT CONVERT(DATETIME, CONVERT(CHAR(9), CURRENT_TIMESTAMP, 112) + '16:30');

SQL Server 2008+:

SELECT CONVERT(DATETIME, CONVERT(DATE, CURRENT_TIMESTAMP)) + '16:30';

SQL Server 2012:

SELECT SMALLDATETIMEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()), 16, 30);
2
votes

Probably the easiest thing to do is to cast the current date/time to a date (stripping the time off), cast it back to a datetime to allow use of datetime's overloaded + (plus) and, finally cast your desired textual time to a datetime. As follows:

select cast(cast(sysutcdatetime() as date) as datetime) + cast('16:30' as datetime)

returns (when run on 11th Jan 2018):

2018-01-11 16:30:00.000
0
votes

You can construct this as you like with day, hour, minute etc.

SELECT CURDATE() - interval 1 DAY + interval 2
0
votes
 select(dateadd(day, datediff(day, 0, getdate()), 0) + '20:00') as specified_date

specified_date - Output Column name

20:00 - Specified time(24 hr Format -Default)

getdate() - To get Today's date.