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

SQL SERVER - Convert Decimal to Time Format in String formatstring

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)

, , , , ,
Previous Post
SQL SERVER – How to Create Linked Server to SQL Azure Database?
Next Post
SQL SERVER – How to Move SQL Server Cluster to Different Domain?

Related Posts

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;

    Reply
  • 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

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

    Reply
  • ‘15.15 instead of 2:15 PM’
    15.15 is 3:15 pm :)

    Reply

Leave a Reply

Menu