Add leading zeroes to time

SELECT RIGHT(CONCAT('000000',13000),6)

When a time is saved as integer the leading zeroes will not be saved. (E.g. 1:30 am => 013000 => 13000). To add the leading zeroes we can use the above snippet. Now we can cast it to our datatype time.

-- for postgres
SELECT RIGHT(CONCAT('000000',13000),6)::time
-- MSSQL needs the additional colon
SELECT CAST(STUFF(STUFF(RIGHT(CONCAT('000000', 13000) ,6),3,0,':'),6,0,':') as time )

Leave a Comment

Your email address will not be published.