All Forums Database
aarsh.dave 24 posts Joined 11/12
11 Dec 2012
Convert integer to TIME

Hi,
 
I need to insert data from a field A which is an integer to field B which is TIME(6).
The data in field A is something like this:
 
1. 1256, which means 12:56:00
2. 0145, which means 01:45:00
 
I want to store it in field B as 12:56:00 and 01:45:00
Can you please help?

11 Dec 2012

Hi,
 
How to compile procedure's ddl which is having UNicode character in ddl.
like below
WHEN sr.std_response_desc LIKE '%要因%'  THEN qa.ans_txt
I tried with bteq but it is giving some junk char.
Then  I tried with QAAQUA Data Studio but it is giving some _unicode 34242523423xcf
SO please let me know what should I do to compile properly.
Thanks,
Abhijeet

Harpreet Singh 101 posts Joined 10/11
11 Dec 2012

Please try this one

select 
cast(1256 as char(4)) as numb, 
substring(numb from 1 for 2)||':'||substring(numb from 3 for 2)||':00' as convertedstring, 
cast (convertedstring as time) as goodtimes

 

mohan.mscss 31 posts Joined 04/11
11 Dec 2012

@ Harpreet,
 
I have tried the above query with the value of 0145 and getting the error as "2620 The format of data contains a bad character"
 
@aarsh,
 
Could you please let me know is there any possibility the data at column a can go beyond 2359? if yes, please let me know how exactly you want to append the data to column B in time format?
 
for example if the data is like 2550 then would you like to store 25:50:00?
And which format do you need either 12 hour or 24 hour format?
Regards,
Mohan K

Harpreet Singh 101 posts Joined 10/11
12 Dec 2012

Nice catch Mohan. Below is what I can do.

select  cast(0145 as char(4)) as num1, 
case when char_length( trim(num1))=4 then num1 else '0'||trim(num1) end as numb, substring(numb from 1 for 2)||':'||substring(numb from 3 for 2)||':00' as convertedstring, 
cast (convertedstring as time) as goodtimes
aarsh.dave 24 posts Joined 11/12
12 Dec 2012

@Mohan,
The values will remain 4 digit, like 1256 or 0330 and it will be in 12 hour format.
 
@Harpreet,
What you say is correct. However, I need to insert the column A in to column B. I am not sure if this would work there.
 
Please let me know what you think.

dnoeth 4628 posts Joined 11/04
12 Dec 2012

This should work:
CAST(TRIM(x (FORMAT '99:99')) AS TIME(0) FORMAT 'hh:Mi')
Dieter

Dieter

aarsh.dave 24 posts Joined 11/12
12 Dec 2012

@Dieter ... It gives an error ... multiple 'FORMAT' options .

dnoeth 4628 posts Joined 11/04
12 Dec 2012

Ops, this is an ODBC issue.
Switch to .NET or BTEQ or switch on the ODBC Driver option "Disable Parsing"
Dieter

Dieter

aarsh.dave 24 posts Joined 11/12
12 Dec 2012

Yes ... it does work in BTEQ ... 
if nothing else works, I will use this one ...
However, am still looking for a more generalised solution :)

ocirej23 10 posts Joined 04/11
30 Jan 2013

 

Hi,

 

I think this will work:

 

SELECT CAST(TRIM(CAST(1256 AS CHAR(4))||'00') AS TIME(0) FORMAT 'HHMISS');

 

Output:

-----------

12:56:00

 

HTH

Cheers,

Eco

14 Apr 2014

Hi there,
i have same problem with minor difference, i have a integer value with integer data type col. i need to convert the integer to time HH:MM:SS.SSSSSS, i need to insert the value as a time. what is is the best cast for converting and which data type coloumn to insert? i appreciate for all comments

dnoeth 4628 posts Joined 11/04
15 Apr 2014

How is the time stored within the INT? 
hhmissssssss?

Dieter

You must sign in to leave a comment.