ANSI Casting Timestamp Netezza - convert SQL from Teradata

Question

I currently do not know ANSI equivalent to Teradata FORMAT key word for converting timestamp, date data types into our required representation formats. I know this can be done with to_char, to_date like individual database specific functions, But I want to write in ANSI so that in future I can easily move code running from one DB to another. Below is the current Teradata SQL I am trying to convert into ANSI so that I can run it on both Teradata ,Netezza and Vertica etc.

SELECT
CAST( (MYTIME ( FORMAT 'DDMMYYYY')) AS CHAR( 8 ))         
|| CAST( (MYTIME (FORMAT 'HHMISS'))   AS CHAR(6))          
|| CAST(CAST(MYNUMBER  AS FORMAT'-9(5)') AS CHAR(5))             
FROM MYTABLE
;

Currently I don't know how to translate the FORMAT 'HHMISS', FORMAT '-9(5)' into ANSI. Is there any documentation on this possible ANSI equivalent functions if any? Please help.

Are you trying to get to a ANSI formatted Timestamp out of multiple fields in Teradata? — Rob Paller
– Rob Paller, Commented Dec 7, 2011 at 20:54
kind of... I am trying to get Teradata's "FORMAT 'HHMISS'" equivalent ANSI which can run on Netezza, Vertica etc... — Enjoy coding
– Enjoy coding, Commented Dec 12, 2011 at 5:12
Should have asked what is the underlying data type for MyTime? — Rob Paller
– Rob Paller, Commented Dec 14, 2011 at 0:31

N West · Accepted Answer · 2012-01-16 16:12:54Z

You can try the EXTRACT() function. YMMV, but most modern RDBMS's support it. There's really no good answer here - every DBMS handles "date formatting" issues differently and confusingly.

select 
case when extract(day from current_timestamp) < 10 then '0' else '' end || cast(extract(day from current_timestamp) as varchar(2)) ||
case when extract(month from current_timestamp) < 10 then '0' else '' end || cast(extract(month from current_timestamp) as varchar(2)) ||
cast(extract(year from current_timestamp) as varchar(4)) ||
case when extract(hour from current_timestamp) < 10 then '0' else '' end || cast(extract(hour from current_timestamp) as varchar(2)) ||
case when extract(minute from current_timestamp) < 10 then '0' else '' end || cast(extract(minute from current_timestamp) as varchar(2)) ||
case when extract(second from current_timestamp) < 10 then '0' else '' end || cast(extract(second from current_timestamp) as varchar(2))

Rob Paller · Accepted Answer · 2011-12-20 19:16:40Z

In Teradata you should be able to do the following:

/* The double cast is to truncate any time value associated with MyTime */
SELECT CAST(CAST(MyTime AS DATE) AS TIMESTAMP(6)) 
     + ((MyTime - TIME '00:00:00') HOUR to SECOND(6)) AS MyTimeStamp_
  FROM MyTable;

Another option to try:

SELECT CreateTimeStamp
     , CAST((CreateTimeStamp (TIMESTAMP(6), FORMAT 'DDMMYYYYBHHMISS.S(6)')) AS CHAR(22)) AS MyTimeStamp_
     , CAST((CreateTimeStamp (TIMESTAMP(6), FORMAT 'DD-MM-YYYYBHH:MI:SS.S(6)')) AS CHAR(26)) AS MyTimeStamp2_
  FROM DBC.Databases
 WHERE DatabaseName = USER;

My experience has been that you have to treat dates and timestamps differently with each database. One solution may not be universally accepted. But I'd love to see something that works in Teradata, Oracle, SQL Server, MySQL, etc.

Collectives™ on Stack Overflow

ANSI Casting Timestamp Netezza - convert SQL from Teradata

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related