By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

Number to Time Conversion

P: n/a
is it possible in sql to convert a 6 digit integer in to a time field
with out converting to character and then substringing
e.g
161201 to 16:12:01

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
SELECT TIME('00:00:00') + CAST(int6 AS DEC(6,0)) "Time"
FROM (VALUES 161201) Q(int6);
------------------------------------------------------------------------------

Time
--------
16:12:01

1 record(s) selected.

OR

SELECT TIME('00:00:00')
+ (int6/10000) Hours
+ MOD(int6/100, 100) Minutes
+ MOD(int6, 100) Seconds AS "TIME"
FROM (VALUES 161201) Q(int6);
------------------------------------------------------------------------------

TIME
--------
16:12:01

1 record(s) selected.

Nov 12 '05 #2

P: n/a
Tonkuma wrote:
SELECT TIME('00:00:00') + CAST(int6 AS DEC(6,0)) "Time"
FROM (VALUES 161201) Q(int6);
------------------------------------------------------------------------------

Time
--------
16:12:01

1 record(s) selected.

OR

SELECT TIME('00:00:00')
+ (int6/10000) Hours
+ MOD(int6/100, 100) Minutes
+ MOD(int6, 100) Seconds AS "TIME"
FROM (VALUES 161201) Q(int6);
------------------------------------------------------------------------------

TIME
--------
16:12:01

1 record(s) selected.

... or encapsulated:
CREATE FUNCTION TIME(int6 INT)
RETURNS TIME
CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC
RETURN
TIME('00:00:00')
+ (int6/10000) Hours
+ MOD(int6/100, 100) Minutes
+ MOD(int6, 100) Seconds

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
cheers !

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.