CODEWITHSUNDEEP
sqldb2
i_am_cris
i_am_cris

Reputation: 627

Strings with date and time to timestamp DB2

I have two fields with strings:

Date_field, Time_field
20001215, 1105
20201215, 1203

How can I create a timestamp with these? This works but I need the time as well:

Select to_date(Date_field, 'yyyy-mm-dd')

Results in:

2000-12-15 00:00:00.0
2020-12-15 00:00:00.0

Desired results:

2000-12-15 11:05:00.0
2020-12-15 12:03:00.0

Any suggestions?

Upvotes: 0

Views: 1891

Answers (2)

axwr
axwr

Reputation: 2226

cristians answer seems better to me, I'd use that over my answer generally. I'll keep mine as it may still help others.

You are most of the way there, obviously it would be nice if you could use two columns in to_date() but as i don't think this i possible then the best alternative is probably to use timestampadd

note the following example from the documentation:

Example 1: The following example will add 40 years to the specified timestamp. An interval of 256 designates years, while 40 specifies the number of intervals to add. The following statement returns the value '2005-07-27-15.30.00.000000'.

SELECT TIMESTAMPADD(256,40,TIMESTAMP('1965-07-27-15.30.00')) FROM SYSIBM.SYSDUMMY1;

So after your existing to_date call use the timestampadd with the value from the time_field on the result of the to_date. That should give you the output you desire.

Upvotes: 0

i_am_cris
i_am_cris

Reputation: 627

Thanks all. Im answering my own question:

Select to_date(Date_field||' '||Time_field,'yyyy-mm-dd HH24:MI:SS')

Results in desired format:

2000-12-15 11:05:00.0
2020-12-15 12:03:00.0

Upvotes: 3

Related Questions