I am casting DateTime field to Time by using CAST Syntax.

select CAST([time] as time) as [CSTTime]

DateTime 2015-03-19 00:00:00.000

Present Output : Time 03:05:36.0000000

I need only HH:MM:SS and not Milliseconds or 0000's

How to filter or Cast it to exact HH:MM:SS Format.

4 upvote
  flag
How on Earth do you get 03:05:36.0000000 from casting 2015-03-19 00:00:00.000 to a TIME format? – Siyual

3 Answers 11

up vote 19 down vote accepted

Time is not stored with its display format in SQL Server.
therefore, from the user perspective, you can say that it has no format.
of course, that's not completely accurate since it does have a storage format, but as an average user you can't really use it.
This is true for all date and time data types:
(Date, DateTimeOffset, DateTime2, SmallDateTime, DateTime and Time).

If you need a format then you don't need to cast to time but to a char. Use Convert to get the char you need:

SELECT CONVERT(char(10), [time], 108) as CSTTime 

Here is some backgroud data if you're interested:

In this article published in 2000 the writer explains in depth how SQL Server treats dates and times. I doubt if anything significant changed between 2000 and 2015 in the way SQL Server stores date, time and datetime values internally.

Here are the relevant quotes, if you don't want to read all of it:

So how does SQL Server internally store the dates? It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers.
........
........
SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms).

since time is actually stored as a 4 byte integer, it really doesn't have a format as an integral part of the data type.

You might also want to check out this article for a more detailed explanation with code samples.

1 upvote
  flag
@suslov Thanks for the formatting improvements. I'll try to live up to this standards in the future. – Zohar Peled

You can achieve it with CAST just simple use TIME(0) datatype in following:

SELECT CAST('2015-03-19 01:05:06.289' AS TIME(0))

OUTPUT:

01:05:06

SQL Server 2008:

select cast(MyDate as time) [time] from yourtable

Earlier versions:

select convert(char(5), MyDate , 108) [time] from yourtable

Other Options:

SELECT CONVERT(VARCHAR(20), GETDATE(), 114)

The simplest way to get the time from datetime without millisecond stack is:

SELECT CONVERT(time(0),GETDATE())

Hour and Minute

SELECT substring(CONVERT(VARCHAR, GETDATE(), 108),0,6) AS Time

Not the answer you're looking for? Browse other questions tagged or ask your own question.