TeradataSQL: Time to String, Add to Date and Compa

Question

I'm trying to figure out the cleanest way to do a comparison in Teradata SQL Assistant. I have the scheduled start date (TimeStamp), the Schedule start time (varchar), actual start and end times (TimeStamp). I need to consolidate the scheduled start date and time and be able to compare it to the actual start and end date and time without modifying the original data (because it's not mine). I realize that the Scheduled Start Time [SST] is in a 24 hour time format with a AM/PM suffix, but like I said before, I can't change that.

I tried to do select cast(substr(scheduled_start_date,1,5) as TIMESTAMP(0)) from DB.TBL but am getting the "Invalid timestamp" error. There is example table data below.

Sch Start Date        Sch Start Time    Actual Start            Actual End
09/11/2017 00:00:00   11:30 AM          09/11/2017  11:34:16    09/11/2017 11:58:00
05/26/2017 00:00:00   15:30 PM          05/26/2017  15:40:00    05/26/2017 15:55:15
11/06/2017 00:00:00   19:30 PM          11/06/2017  21:25:00    11/06/2017 21:45:00

Thanks!

Answer 1

A couple things to try:

1. Convert the separate Scheduled Date and Scheduled Time fields into strings, concatenate them, and feed that into a TIMESTAMP CAST. Something like:

   SELECT CAST(CAST(Scheduled_Date AS DATE) AS VARCHAR(25)) AS Date_String, CAST(CAST(Scheduled_Time AS TIME FORMAT 'HH:MM BB') AS VARCHAR(25)) AS Time_String, CAST(TRIM(Date_String) || ' ' || TRIM(Time_String) AS TIMESTAMP(0)) AS MyTimestamp

2. Cast the Scheduled Time field as a TIME data type. Cast the Scheduled Date field as a DATE data type. Then somehow combine the two into a TIMESTAMP field -- either with a CAST or some kind of timestamp constructor function (not sure if this is possible)

Option 1 should work for sure as long as you properly format the strings. Try to avoid using SUBSTRING and instead use FORMAT to cast as DATE/TIME fields. Not sure about Option 2. Take a look at these link for how to format DATE/TIME fields using the FORMAT clause:

https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference%2FB035-1143-160K%2Fmuq1472241377538.html%23wwID0EPHKR

https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1143-160K/cmy1472241389785.html

Sorry, I don't have access to a TD system to test it out. Let me know if you have any luck.

Answer 2

You need to cast the schedule start time as an Interval, then you can easily add it to the start date:

scheduled_start_date
+ Cast(Substr(scheduled_start_time, 1,5) AS INTERVAL HOUR TO MINUTE)

A start date which is a timestamp seems to indicate this was ported from Oracle/SQL Server?

And a 24 hour time format with a AM/PM suffix is also quite strange.