Formatting Time Of A Date Time Field

Inigo Montoya 586 Reputation points

I have a datetime field that is in this format 2020-01-15 14:49:46.6830000 I have been using cast(fieldname as Time) to get the time element only from the field.

I have a new requirement where I only want to return 14:49:46. How would I write my sql to only return that part of the time?

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,523 questions
0 comments No comments
{count} votes

3 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,081 Reputation points

    Good day,

    I have a datetime field that is in this format 2020-01-15 14:49:46.6830000

    You are totally wrong. You cannot have "datetime field that is in this format 2020-01-15 14:49:46.6830000" sine this is the way the client application display/format the value while in the column in the table the format is totally different.

    This is very common mistake which lead to question like yours. You should understand the different between Date displaying format, vs Date storing format !

    Check this link as well.

    Back to your needs:

    The short answer is that you can use FORMAT (@DT2, 'HH:mm:ss')

    A full example with some explanation:

    -- '2020-01-15 14:49:46.6830000' this is string and NOT datetime!!  
    -- Always use explicit CONVERT with the right style!  
    -- The third parameter of the convert function is optional. Unfortunately poor DBA do not use it, so the server need to guess how to make the convert  
    SET @DT2 = CONVERT(DATETIME2, '2020-01-15 14:49:46.6830000', 121)  
    -- Now in the database we have a real DATETIME2 and NOT a string  
    -- But when we use SELECT without CONVERT then again, the server explicitly convert the value to string  
    -- the next line returns string and NOT DATETIME2 !!!   
    SELECT @DT2  
    -- The next line returns what you want. We can use CONVERT with explicit style or the function format.  
    -- Both option will return string and NOT datetime2  
    SELECT FORMAT (@DT2, 'HH:mm:ss')  
    0 comments No comments

  2. Erland Sommarskog 99,461 Reputation points MVP

    The others make some good points. Date and time data types do not have any format. They are stored by an internal (binary) representation.

    But going back to what you were trying to do, you were close: This gives you what you are looking for cast(fieldname as Time(0))

    0 here is the scale, that is how many figures you want after the second. Time(3) gives you milliseconds. time or time(7) gives you time a precision of 100 ns - something you have very rarely need for.

    I would suggest that for time you use time(0) when you have no other requirement and datetime2(3) for that data type. (Because most often you are capturing date time inside SQL Server and the accuracy in that case is only by the millisecond.

    0 comments No comments