Try convert(varchar(max), fieldname, 108).
Formatting Time Of A Date Time Field
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?
3 additional answers
Sort by: Most helpful
-
Ronen Ariely 15,191 Reputation points
2020-10-14T17:31:33.83+00:00 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 !
Back to your needs:
The short answer is that you can use
FORMAT (@DT2, 'HH:mm:ss')
A full example with some explanation:
DECLARE @DT2 DATETIME2 -- '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')
-
Tom Phillips 17,736 Reputation points
2020-10-14T18:43:16.45+00:00 -
Erland Sommarskog 112.7K Reputation points MVP
2020-10-14T21:39:20.467+00:00 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.