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.

Query and Collation problem

I am querying against a Linked Server, the following statement:
SELECT * FROM Table
WHERE name_field LIKE 'José%'
The "name_field" field, is a varchar(50) with SQL_Latin1_General_CP1_CI_AI...
it's Accent Insensitive, what is right... So, when I send this query, I
receive all the 'Jose' and 'José' regs...
I have a site that does the same query, from another server, using linked
server resource... The query is almost the same:
SELECT * FROM [Server].Database.Owner.Table
WHERE name_field LIKE 'José%'
This time, looks like it's using Accent Sensitive, because it don't bring
just the 'José' regs...
It's an Asp app who is querying this way...
Does someone know what can It be?
Thx,
Rafa?
What SQL Server versions are these - the ones being used in the Linked
Server?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query and Collation problem

I am querying against a Linked Server, the following statement:
SELECT * FROM Table
WHERE name_field LIKE 'José%'
The "name_field" field, is a varchar(50) with SQL_Latin1_General_CP1_CI_AI...
it's Accent Insensitive, what is right... So, when I send this query, I
receive all the 'Jose' and 'José' regs...
I have a site that does the same query, from another server, using linked
server resource... The query is almost the same:
SELECT * FROM [Server].Database.Owner.Table
WHERE name_field LIKE 'José%'
This time, looks like it's using Accent Sensitive, because it don't bring
just the 'José' regs...
It's an Asp app who is querying this way...
Does someone know what can It be?
Thx,
Rafa®What SQL Server versions are these - the ones being used in the Linked
Server?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query and Collation problem

I am querying against a Linked Server, the following statement:
SELECT * FROM Table
WHERE name_field LIKE 'José%'
The "name_field" field, is a varchar(50) with SQL_Latin1_General_CP1_CI_AI..
.
it's Accent Insensitive, what is right... So, when I send this query, I
receive all the 'Jose' and 'José' regs...
I have a site that does the same query, from another server, using linked
server resource... The query is almost the same:
SELECT * FROM [Server].Database.Owner.Table
WHERE name_field LIKE 'José%'
This time, looks like it's using Accent Sensitive, because it don't bring
just the 'José' regs...
It's an Asp app who is querying this way...
Does someone know what can It be?
Thx,
Rafa?What SQL Server versions are these - the ones being used in the Linked
Server?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.