I am a total newbie at Teradata, and i have to write a query that I can't easily test on a Teradata system (someone else is running the query for me, it takes weeks for them to get back to me with errors, etc.) So I sincerely apologize in advance if this question is silly or repetitive, but I couldn't find the answer online, and i really need an expert's opinion!
I'm getting an error in the following sub-query:
cast(DATE_DT as date format 'yyyymmdd')(char(8)) AS TXN_DTE
,CAST(USER_ID AS VARCHAR(9)) + ' ' + CAST(STORE_NBR AS VARCHAR(5)) +' ' +CAST(DATE_DT AS timestamp(0) format 'YYYYMMDDHHMISS') (char(16)) AS TRANSACTION_ID
The error I get is:
5407: Invalid operation on an ANSI Datetime or Interval value.
Output directed to Answerset window
I realize that this has to do with the way I'm casting the datetime field DATE_DT to date in line 2 (where i'm only trying to chop off the "time" component) or to char in line 4 (where i want to keep the time component, but convert the entire thing to char). I'm lost on how to fix it.
Any help would be greatly appreciated!
You seem to be using '+' as a concat operator, which in Teradata must be '||'.
You don't provide the table definition, but your query will fail if the column DATE_DT is a timestamp(6) (for example).
Thanks, Carlos! Entries in the DATE_DT column look like this: 2010-01-29 18:39:00.000
Aside from the concat operators, why do you say the query would fail, and how do i fix it?
It's TIMESTAMP(3) then...
SELECT CAST(CAST(DATE_DT AS FORMAT 'YYYYMMDD') AS CHAR(8)) AS TXN_DTE,
CAST(USER_ID AS VARCHAR(9)) || ' ' ||
CAST(STORE_NBR AS VARCHAR(5)) || ' ' ||
CAST(CAST(DATE_DT AS FORMAT 'YYYYMMDDHHMISS') AS CHAR(14))