A very strange question I received the other day in an email about how to convert decimal to time format string.
Here is the email I received:
“Pinal,
I have a very strange issue with string formatting.
I have a string like 5.07 which I need to convert to 5:07. Can you help?”
Indeed a very strange question as I have never faced such a question before. However, when you think of it, it totally makes sense. I have seen many countries display time as 15.15 instead of 2:15 PM and there can be scenarios when people want to convert to dot to the colon.
Here is the simplest possible script where you can convert the date to the colon.
DECLARE @value DECIMAL(4,2) = 5.07 SELECT @value OriginalValue, REPLACE(RIGHT(@value + 1000, 5), '.', ':') NewValue
Well, that’s it. There is no since this is a very simple script where I have replaced the dot with a colon. Another, cool trick would be to assign this value to string and do the string replace. Either way both will work. However, this function has been written keeping in the mind that the column was original decimal or numeric.
Let me know if your country follows dot or colon to display time.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
We can also use format function,
DECLARE @value DECIMAL(4,2) = 5.10;
SELECT @value AS OriginalValue,
REPLACE(FORMAT(@value, ‘0#.00’), ‘.’, ‘:’) AS NewValue,
REPLACE(FORMAT(IIF(@value>=12, @value-12, @value), ‘0#.00’), ‘.’, ‘:’) + IIF(@value>=12, ‘ PM’, ‘ AM’) AS NewAPMValue;
SET @value = 17.03
SELECT @value AS OriginalValue,
REPLACE(FORMAT(@value, ‘0#.00’), ‘.’, ‘:’) AS NewValue,
REPLACE(FORMAT(IIF(@value>=12, @value-12, @value), ‘0#.00’), ‘.’, ‘:’) + IIF(@value>=12, ‘ PM’, ‘ AM’) AS NewAPMValue;
This may look a little complex, but I think it will get the better results
DECLARE @value DECIMAL(4,2) = 5.07
SELECT CAST(DATEADD(HOUR,@value, DATEADD(MINUTE, (@value%1)*100, cast(0 as datetime))) AS Time)
If your decimal value is true decimal means 5.5 is 5 hours 30 minutes, than in above line you can multiply by 60 instead of 100
DateTimeOffset = ToDateTimeOffset( Cast( 0 AS DateTime ),
CASE
WHEN ( TimeZoneOffset >= 0 )
THEN ‘+’
ELSE ‘-‘
END +
Cast( Cast( DateAdd( Minute, 60 * Abs( TimeZoneOffset ), 0 ) AS Time ) AS varchar( 5 ) )
)
‘15.15 instead of 2:15 PM’
15.15 is 3:15 pm :)