Converting Netezza timestamp to Julian Day Number

Question

I have been looking for it during days but could not find how to do..

It is like:

select to_number(to_char('2015-06-24 00:00:00','J')) on Oracle.

I need to find the Julian Numeric Day value, not to be confused with the ordinal date of the year..

Answer 1

Conversion templates indicate that 'J' is exactly what you want.

I think the issue you have is the to_number() function, not the to_char() function. Use casts instead.

SYSTEM(ADMIN)=> select to_char('2015-06-24 00:00:00'::timestamp,'J')::int;
 ?COLUMN?
----------
  2457198
(1 row)