Need help on converting Integer and date to timestamp column

Database
Enthusiast

Need help on converting Integer and date to timestamp column

create table db_name.test

(Starttime INTEGER,

Row_Date DATE FORMAT 'YYYY-MM-DD' )

insert into db_name.test values(1520,'2013-09-11');

insert into db_name.test values(830 ,'2012-05-21');

insert into db_name.test values(400,'2012-03-19');

insert into db_name.test values(1625,'2012-07-07');

insert into db_name.test values(30,'2014-03-19');

insert into db_name.test values(35,'2014-08-19');

insert into db_name.test values(0,'2014-08-19');

output will be in timestamp after combining this date and time:

2013-09-11 15:20:00

2012-05-21  08:30:00

2012-03-19  04:00:00

2012-07-07  16:25:00

2014-03-19  00:30:00

2014-08-19  00:35:00

2014-08-19  00:00:00

2 REPLIES
Junior Supporter

Re: Need help on converting Integer and date to timestamp column

Hi:

You could try :

SELECT CAST(ROWDATE AS TIMESTAMP(0))+

CAST(STARTTIME/100 AS INTERVAL HOUR(2))+

CAST(STARTTIME MOD 100 AS INTERVAL MINUTE(2))

FROM DB_NAME.TEST

HTH

Cheers.

Carlos.

Enthusiast

Re: Need help on converting Integer and date to timestamp column

Hi Carlos,

Its working for me. Thanks a lot.. you are genius :)

I want to do reverse of this one as well.

Input as timestamp :

2013-09-11 15:20:00

2012-05-21  08:30:00

2012-03-19  04:00:00

2012-07-07  16:25:00

2014-03-19  00:30:00

2014-08-19  00:35:00

2014-08-19  00:00:00

Output will be in two columns as below:

row_date(date)    starttime(integer)

2013-09-11            1520       

2012-05-21            830        

2012-03-19            400

2012-07-07            1625

2014-03-19            30

2014-08-19            35

2014-08-19            0

Thanks,

Deepak