[Solved] How to convert varchar time to 24hours Time in SQL Server [closed]

by


Not clear whether you want your result as a VARCHAR or an actual TIME type, so here’s both:

DECLARE @Table TABLE (
    TXN_TIME VARCHAR(6)
)

INSERT INTO @Table
SELECT '053124'
UNION
SELECT '173932'
UNION
SELECT '011815'
UNION
SELECT '120349'
UNION
SELECT '134207'

SELECT  TXN_TIME,
        LEFT(TXN_TIME,2) + ':' + SUBSTRING(TXN_TIME,3,2) + ':' + SUBSTRING(TXN_TIME,5,2) + ':000000' AS Converted,
        CONVERT(TIME(6),LEFT(TXN_TIME,2) + ':' + SUBSTRING(TXN_TIME,3,2) + ':' + SUBSTRING(TXN_TIME,5,2)) AS ConvertedTime
FROM @Table

Results:

/----------------------------------------------\
| TXN_TIME |    Converted    |  ConvertedTime  |
|----------+-----------------+-----------------|
|  011815  | 01:18:15:000000 | 01:18:15.000000 |
|  053124  | 05:31:24:000000 | 05:31:24.000000 |
|  120349  | 12:03:49:000000 | 12:03:49.000000 |
|  134207  | 13:42:07:000000 | 13:42:07.000000 |
|  173932  | 17:39:32:000000 | 17:39:32.000000 |
\----------------------------------------------/

0

solved How to convert varchar time to 24hours Time in SQL Server [closed]