# SQL SERVER – Convert Decimal to Time Format in String

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)

#### Related Posts

• rainmaker
September 13, 2017 12:33 pm

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;

• Mudit Agarwal
September 14, 2017 7:34 pm

This may look a little complex, but I think it will get the better results

DECLARE @value DECIMAL(4,2) = 5.07

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

• SQL Server Performance Tune
September 14, 2017 9:22 pm

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 ) )
)

• 