0

I'm trying to convert a date key and a time key (both numeric) to a timestamp in yyyy-mm-dd hh:mm:ss format in Netezza.

e.g. date_key=20120711 time_key=61946 should be converted to '2012-07-11 06:19:46'

I tried to_date function 

to_date(date_key||to_char(time_key,'099999'),'yyyymmdd hh24miss').

The function works in Oracle but fails in Netezza only producing a date result: '2012-07-11 00:00:00' for the above example. What would work in Netezza? Thanks.

user3889909
  • 1
  • 1
  • 2

1 Answers1

2

That logic works however in Netezza you need to call the to_timestamp function to include time.

select test.datekey
, to_timestamp( test.datekey||trim(to_char(test.timekey,'099999')),'YYYYMMDDHH24MISS')
, to_timestamp( test.datekey||to_char(test.timekey,'099999'),'YYYYMMDD HH24MISS')
from (select cast(20120711 as integer) as datekey, cast(61946 as integer) as timekey ) as test
Niederee
  • 4,155
  • 25
  • 38
  • It's worth noting that what Oracle refers to as a DATE is actually stored internally as what we would think of as a TIMESTAMP, and that's why the to_date function works as it does in Oracle. – ScottMcG Jul 30 '14 at 19:06