Many a times we may need to get the Time, Hour, Minute, Second and Millisecond Part from DateTime in Sql Server. In this article we will see how we can get these parts of the DateTime in Sql Server.
You may like to read the other popular articles on Date and Time:
- How to get Date Part only from DateTime in Sql Server
- How to get Day, Month and Year Part from DateTime in Sql Server
- Difference between DateTime and DateTime2 DataType
1. TIME part of DateTime in Sql Server
Following demos shows how to get some of the commonly required Time Part format from a DateTime.
Demo 1: Time in the 24-hour format hh:mi:ss
SELECT GETDATE() 'Today',
CONVERT(VARCHAR(8), GETDATE(), 108) 'hh:mi:ss'
RESULT:
Demo 2: Time in the 24-hour format hh:mi:ss:mmm
SELECT GETDATE() 'Today', CONVERT(VARCHAR(12),GETDATE(),114) 'hh:mi:ss:mmm'
RESULT:
Demo 3: Time in the 12-hour format hh:mi:ss:mmmAM (or PM)
SELECT GETDATE() 'Today', RIGHT(CONVERT(VARCHAR(26), GETDATE(), 109),14) 'hh:mi:ss:mmmAM (or PM)'
RESULT:
Demo 4: Time in the 12-hour format hh:miAM (or PM)
SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))
RESULT:
Demo 5: Time in the 24-hour format hh:miAM (or PM)
SELECT GETDATE() 'Today', CONVERT(VARCHAR(5), GETDATE(), 108) + (CASE WHEN DATEPART(HOUR, GETDATE()) > 12 THEN 'PM' ELSE 'AM' END) 'hh:miAM (or PM)'
RESULT:
Demo 6: Time in the 24-hour format hh:mm:ss.nnnnnnn
Note this script will work in sql Server 2008 and above as here I am using TIME datatype and SYSDATETIME() functions which were introduced in Sql Server 2008.
SELECT GETDATE() 'Today', CAST(SYSDATETIME() AS TIME) 'hh:mm:ss.nnnnnnn'
RESULT:
2. HOUR part of the DateTime in Sql Server
We can use DATEPART() function to get the HOUR part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as hour or hh.
SELECT GETDATE() 'Today', DATEPART(hour,GETDATE()) 'Hour Part' SELECT GETDATE() 'Today', DATEPART(hh,GETDATE()) 'Hour Part'
RESULT:
3. MINUTE part of the DateTime in Sql Server
We can use DATEPART() function to get the MINUTE part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as minute or mi or n.
SELECT GETDATE() 'Today',
DATEPART(minute,GETDATE()) 'Minute Part'
SELECT GETDATE() 'Today', DATEPART(mi,GETDATE()) 'Minute Part'
SELECT GETDATE() 'Today', DATEPART(n,GETDATE()) 'Minute Part'
RESULT:
4. SECOND part of the DateTime in Sql Server
We can use DATEPART() function to get the SECOND part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as second or ss or s.
SELECT GETDATE() 'Today',
DATEPART(second,GETDATE()) 'Second Part'
SELECT GETDATE() 'Today', DATEPART(ss,GETDATE()) 'Second Part'
SELECT GETDATE() 'Today', DATEPART(s,GETDATE()) 'Second Part'
RESULT:
5. MILLISECOND part of the DateTime in Sql Server
We can use DATEPART() function to get the MILLISECOND part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as millisecond or mi .
SELECT GETDATE() 'Today',
DATEPART(millisecond,GETDATE()) 'MilliSecond Part'
SELECT GETDATE() 'Today',
DATEPART(ms,GETDATE()) 'MilliSecond Part'
RESULT:
Thank you Mr. Biradar this was really helpful.
That was extremely helpful. This is how to replace a null date with a new date and the current time: ISNULL(,CAST((‘2010-1-1 ‘+CONVERT(VARCHAR(8), GETUTCDATE(), 108)) AS DATETIME)). This is useful when you store as utc and convert to local time for display.
Jeje! Amigo, eres un kurdo.
Excelente tutorial. Buena explicación y dinámica.
Me sirvió. Agradezco tu consejo.
Saludos.
Thank you.
Excelente, mejor explicación no pude encontrar.
Superb tutorial.. Really Helpful.
Dear Mr. Biradar, kindly need your help (I’m sorry for my bad English),
I’m already get result time using ‘Demo 1’ for example Time1 and Time2, but the datatype result of ‘Demo 1’ is in varchar.
My Question is how to subtract between two times ?
Thanks for your help
eko
Thank you so much..very helpful..thanks a alot
I have float value 1.09796836411745 and need output in time 02:21:0463 …
how to get output using sql select ..i.e. select floattotime from table..
Sir You have provided very precious information regarding the DateTime Function ….Thank Yo Sir
Thank you Alok
I have been searching for days trying to find something to convert time from 24 hour to 12 hour. Most people said it couldn’t be done or they provided solutions that didn’t work. This did the trick so thank you so much!!!
Thank you Cindy. Happy that it helped you…
Really Very Helpfull..!! Thanks Alot
Thank you!
This article is very helpful
DECLARE @year AS INT
SET @year=2012
select 365+(CASE WHEN @year%4=0 THEN 1 ELSE 0 END) AS [TotalDays]
Hello, Sir
Really Very Help For Me.
Thanks.
Very helpful, very informality, good demo
Can you help me with using FORMAT Function
FORMAT(GETDATE(),’hh:mm’) is always return 12 hour time i want it in 24 hour
Hello ,
I am trying to insert this value “2021-06-22 22:40:53.1855801” in SQL server but I got result in SQL server is like “2021-06-22 22:40:53.400”.
For this I have use syntax.
DateTime cs_myDateTime = Convert.ToDateTime(rd[1].ToString());
string sql = “INSERT INTO ProcessValues([DeviceId],[TimeStamp]) Values(‘” + cs_myDateTime.ToString(“yyyy-MM-dd HH:mm:ss.nnnnnnn”) + “‘);