Casting timestamp to date and to char

Database

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!

4 REPLIES
Junior Supporter

Re: Casting timestamp to date and to char

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.

Re: Casting timestamp to date and to char

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?

Junior Supporter

Re: Casting timestamp to date and to char

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.

Re: Casting timestamp to date and to char

Thanks so much for your help!