Geek asks and answers

Build a DateTime using today's date at a specific time

I would like to get 4:30 pm of the current day. Hard coding this way does not work:

SELECT '07242012 16:30:00.000'

It turned out to be harder than I thought. How do I approach this?

+6
source share
4 answers

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);
+20
source

Probably the easiest way is to make the current date / time to date (turn off the time), return it back to datetime to allow the use of an overloaded datetime, + (plus)and finally specify the desired text time to date. As below:

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

returns (when launched on January 11, 2018):

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

, , ..

SELECT CURDATE() - interval 1 DAY + interval 2
0
 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 date.

0

More articles:


All Articles