Query DB2 from SSRS and Convert Integer to Time

Hi,

I am querying DB2 from SSRS. I get an interger back that represents a time like this: HHMMSS

(The data type in DB2 is an integer.) I would like to make this representation of the time display a little more friendly. Does anyone have a good idea on how I can change the query or SSRS format to display semi-colons to break up the hours, minutes and seconds? Also, the other issue is that since I don't get leading zeros back....they probably need to be added to this value if the value is not a full 6 characters.

Here is one approach to this issue.

DECLARE @.MyTime int
SET @.MyTime = 63000

SELECT cast( stuff( stuff( right( '0' + cast( @.MyTime AS varchar(6) ), 6 ), 3, 0 , ':'), 6, 0, ':' ) AS datetime )

If this is a regular occurance, you may wish to create a FUNCTION for this task.

|||

Thanks. It looks like DB2 does not like the STUFF command, so I will try to apply this concept after I find a similar DB2 command.