CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2type-conversion
Jacob Jolliff
Jacob Jolliff

Reputation: 47

How to convert varchar to time hh:mm

I have varchars representing military times:

a 2130 -- 21 hours
b 103 -- 1 hour
c 10  -- 10 minutes
d 1  -- 1 minutes

the left two characters always represent minutes. In example c and c above 10 and 1 are always minutes. example b 103 has three characters. 1 is hour 03 is minute in this case.

How do I convert this into time hh:mm format?

Upvotes: 1

Views: 2204

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81990

One option is use to use Format()

Example

Declare @YourTable table (SomeCol int)
Insert into @YourTable values
 (2130)
,(103)
,(10)
,(1)

Select SomeCol
      ,TimeValue = format(SomeCol,'00:00')
 From  @YourTable

Returns

SomeCol TimeValue
2130    21:30
103     01:03
10      00:10
1       00:01

EDIT - Requested EDIT for 2008

Declare @YourTable table (SomeCol int)
Insert into @YourTable values
 (2130)
,(103)
,(10)
,(1)

Select SomeCol
      ,TimeValue = stuff(right('0000' + left(SomeCol, 4),4), 3, 0, ':')
 From  @YourTable

Upvotes: 3

Ronen Ariely
Ronen Ariely

Reputation: 2434

Good day Jacob,

Please check if this solution fit you:

Declare @YourTable table (SomeCol int)
Insert into @YourTable values
 (2130)
,(103)
,(10)
,(1)
--Select SomeCol,TimeValue = format(SomeCol,'00:00'), SQL_VARIANT_PROPERTY (format(SomeCol,'00:00'),'BaseType')
--From  @YourTable
SELECT CONVERT(TIME(0),TIMEFROMPARTS(ROUND(SomeCol/100,0), SomeCol%100,0,0,0))
FROM @YourTable

Ops, I notice new information in the comments. Seems like you use SQL Server 2008R2 and TIMEFROMPARTS will not work on SQL Server2008r2 (only starting with 2012)...I will edit the answer in a second

Note! I highly recommend to re-think about your database design.Please read the comment I wrote above for more information

Update:

-- For old servers please check if one of these this fit you
SELECT --RIGHT('00' + CONVERT(VARCHAR(2),ROUND(SomeCol/100,0)),2),RIGHT('00' + CONVERT(VARCHAR(2),SomeCol%100),2),
    CONVERT(TIME(0),RIGHT('00' + CONVERT(VARCHAR(2),ROUND(SomeCol/100,0)),2) + ':' + RIGHT('00' + CONVERT(VARCHAR(2),SomeCol%100),2))
FROM @YourTable

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can try the following using length of column value and case statement.

Declare @YourTable table (SomeCol int)
Insert into @YourTable values
 (2130)
,(103)
,(10)
,(1)

Select 
    case LEN(SomeCol)
        when 4 then SUBSTRING('2130', 1, 2) + ':' + SUBSTRING('2130', 3, 2)
        when 3 then '0' + SUBSTRING('103', 1, 1) + ':' + SUBSTRING('103', 2, 2)
        when 2 then '00:' + CONVERT(Varchar(5), SomeCol)
        else '00:0' + CONVERT(Varchar(5), SomeCol)
    end as TimeValue
from @YourTable

Upvotes: 0

Related Questions