question

jaymz69 avatar image
jaymz69 asked

Convert numeric data to a time format

I need to take the data from DB2 (which is numeric) 104522 and format it as the time it is, 10:45:22 How can I accomplish this in SQL Server 2008 R2?
sql-server-2008-r2db2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
SirSQL avatar image
SirSQL answered
This will do it for an individual value DECLARE @DB2Time NUMERIC(6, 0) = 104522 SELECT CAST( SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),@DB2Time),6),1,2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),@DB2Time),6),3,2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),@DB2Time),6),5,2) AS TIME) The SUBSTRING(RIGHT is used to ensure that you get a valid time even if less numbers are passed (due to it being before 10am). If pulling from a table SELECT CAST( SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),DB2Time),6),1,2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),DB2Time),6),3,2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),DB2Time),6),5,2) AS TIME) FROM YourDB2Table
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Good one. This is something you have to do all too often if you're investigating the msdb job schedules & job history tables!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.