54210

TeradataSQL: Time to String, Add to Date and Compare to Another Time and Data

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!

Answer1:

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 <em>date</em> which is a <em>timestamp</em> seems to indicate this was ported from Oracle/SQL Server?

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

Answer2:

A couple things to try:

<ol><li>

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

</li> <li>

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)

</li> </ol>

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:

<a href="https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference%2FB035-1143-160K%2Fmuq1472241377538.html%23wwID0EPHKR" rel="nofollow">https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference%2FB035-1143-160K%2Fmuq1472241377538.html%23wwID0EPHKR</a>

<a href="https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1143-160K/cmy1472241389785.html" rel="nofollow">https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1143-160K/cmy1472241389785.html</a>

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

Recommend

  • Saxon produces empty XML from Schematron
  • Simple Normalizing Text Example Does Not Work
  • Deep link into eBay iOS app with custom URL scheme?
  • PostgreSQL - make two transactions run at the same time
  • Will changing column from timestamp to timestamptz lock the table?
  • How to implement a filter from another stream using RxJava
  • How to get average values for time intervals in Postgres
  • RavenDB indexing errors
  • Multiple Left Join LINQ-to-entities
  • Suqueries in select clause with JPA
  • Default parameter as generic type
  • CakePHP ACL tutorial initDB function warnings
  • Query to find the duplicates between the name and number in table
  • Stop Bash Script if Hive Fails
  • Eloquent update method change created_at timestamp
  • Why querying a date BC is changed to AD in Java?
  • Row Count Is Returning the incorrect number using RaptureXML
  • Exception “firebase.functions() takes … no argument …” when specifying a region for a Cloud Function
  • Using variable in a value field in jMeter
  • Illegal mix of collations for operation for date/time comparison
  • Release, debug version and Authorization Google?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Delete MySQLi record without showing the id in the URL
  • Calling of Constructors in a Java
  • Numpy divide by zero. Why?
  • Unanticipated behavior
  • Traverse Array and Display in markup
  • Transpose CSV data with awk (pivot transformation)
  • Comma separated Values
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • Trying to get generic when generic is not available
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • How does Linux kernel interrupt the application?
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • How can I use threading to 'tick' a timer to be accessed by other threads?