Issue while casting BYEINT column to Timestamp

Database
Enthusiast

Issue while casting BYEINT column to Timestamp

Hi,

I am trying to derive a TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss'. I have DATE(cre_dt) part in one column and HOUR(cre_hr), MIN(cre_min), SEC(dispute_case_cre_sec) part each in 3 different columns. When i try to cast iam getting invalid time stamp error.

The datatypes of the columns are:

cre_dt :-DATE            FORMAT 'yyyy-mm-dd'

cre_hr :-BYTEINT

cre_min :- BYTEINT

cre_sec :- BYTEINT

SELECT
CAST(
CAST(cre_dt AS CHAR(10)) || ' ' || CAST(cre_hr AS CHAR(2)) || ':' ||
CAST(cre_min AS CHAR(2)) || ':' || CAST(cre_sec AS CHAR(2)) AS TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss')

FROM table

Thanks

Sitara

3 REPLIES
Enthusiast

Re: Issue while casting BYEINT column to Timestamp

SELECT

CAST(

      CAST(CAST(cre_dt AS CHAR(10)) || ' ' || CAST(cre_hr AS CHAR(2)) || ':' ||

      CAST(cre_min AS CHAR(2)) || ':' || CAST(cre_sec AS CHAR(2))) AS VARHCAR(19)) AS TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss')

      

      FROM table

 

Try this.

Cheers,

BM

Teradata Employee

Re: Issue while casting BYEINT column to Timestamp

Default format for BYTEINT -ZZ9 does not include leading zeros and the implicit truncation trims spaces, so instead of e.g. '07' for 7AM you get '7 '

 

SELECT

CAST(

CAST(cre_dt AS CHAR(10)) || ' ' || CAST(CAST(cre_hr AS FORMAT '99') AS CHAR(2)) || ':' ||

CAST(CAST(cre_min AS FORMAT '99') AS CHAR(2)) || ':' || CAST(CAST(cre_sec AS FORMAT '99') AS CHAR(2))

AS TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss')

 

FROM table

Enthusiast

Re: Issue while casting BYEINT column to Timestamp

Thanks Fred, it worked.