Convert integer to TIME

Database
Enthusiast

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?

Tags (3)
12 REPLIES
Enthusiast

Re: Convert integer to TIME

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

Enthusiast

Re: Convert integer to TIME

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
Enthusiast

Re: Convert integer to TIME

@ 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

Enthusiast

Re: Convert integer to TIME

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

Enthusiast

Re: Convert integer to TIME

@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.

Junior Contributor

Re: Convert integer to TIME

This should work:

CAST(TRIM(x (FORMAT '99:99')) AS TIME(0) FORMAT 'hh:Mi')

Dieter

Enthusiast

Re: Convert integer to TIME

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

Junior Contributor

Re: Convert integer to TIME

Ops, this is an ODBC issue.

Switch to .NET or BTEQ or switch on the ODBC Driver option "Disable Parsing"

Dieter

Enthusiast

Re: Convert integer to TIME

Yes ... it does work in BTEQ ... 

if nothing else works, I will use this one ...

However, am still looking for a more generalised solution :)