Teradata Concatenate multiple string columns format as timestamp

Question

I am still fairly new to Teradata so forgive this but I have two columns one with the date and one with a 4 digit varchar as the time (24hour)

below is what I use to concatenate the fields to make it readable but I want to make the result come out as a valid timestamp so I can perform calculations.

cast(SCHEDULE_DATE as date format 'yyyy-mm-dd') || ' ' || substr(START_TIME,0,3) || ':' || substr(START_TIME,2,2)

This is an example of the results I get from the above query. 2017-01-25 13:30

when I run the query like this

cast(cast(SCHEDULE_DATE as date format 'yyyy-mm-dd') || ' ' || substr(START_TIME,0,3) || ':' || substr(START_TIME,2,2) as Timestamp ) as TESTVALUE

I get invalid TimeStamp

David דודו Markovitz David דודו Markovitz · Accepted Answer · 2017-02-15T20:35:09

select  '2017-02-15'    as schedule_date
       ,'2233'          as start_time
       ,to_timestamp (schedule_date || start_time,'yyyy-mm-ddhh24mi')    as ts
;

+---------------+------------+----------------------------+
| schedule_date | start_time | ts                         |
+---------------+------------+----------------------------+
| 2017-02-15    | 2233       | 2017-02-15 22:33:00.000000 |
+---------------+------------+----------------------------+

P.s.
The substr arguments are wrong.
Teradata uses 1 as start point.

select  '1234'                  as start_time
       ,substr(start_time,0,3)  as original_1
       ,substr(start_time,2,2)  as original_2
       ,substr(start_time,1,2)  as correct_1
       ,substr(start_time,3,2)  as correct_2
;

+------------+------------+------------+-----------+-----------+
| start_time | original_1 | original_2 | correct_1 | correct_2 |
+------------+------------+------------+-----------+-----------+
| 1234       | 12         | 23         | 12        | 34        |
+------------+------------+------------+-----------+-----------+

Teradata Concatenate multiple string columns format as timestamp

1 Answers