All Forums Database
SofiaVer 3 posts Joined 11/12
15 Nov 2012
Casting timestamp to date and to char

Hi all,

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:
   

SELECT
  cast(DATE_DT as date format 'yyyymmdd')(char(8)) AS TXN_DTE
 , USER_ID
 ,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
FROM Table

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!

CarlosAL 512 posts Joined 04/08
15 Nov 2012

Sofia:
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).
HTH.
Cheers.
Carlos.

SofiaVer 3 posts Joined 11/12
15 Nov 2012

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?
 

CarlosAL 512 posts Joined 04/08
16 Nov 2012

Sofia:
It's TIMESTAMP(3) then...
SELECT CAST(CAST(DATE_DT AS FORMAT 'YYYYMMDD') AS CHAR(8)) AS TXN_DTE,
       USER_ID,
       CAST(USER_ID AS VARCHAR(9)) || ' ' ||
       CAST(STORE_NBR AS VARCHAR(5)) ||  ' ' ||
       CAST(CAST(DATE_DT AS FORMAT 'YYYYMMDDHHMISS') AS CHAR(14))
  FROM TABLE
;
HTH.
Cheers.
Carlos.

SofiaVer 3 posts Joined 11/12
16 Nov 2012

Thanks so much for your help!

You must sign in to leave a comment.