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..

Please clarify what you mean by "Julian". The current Julian Day Number is 2457203; it's the number of days since January 1, 4713 BCE ([reference](https://en.wikipedia.org/wiki/Julian_day). The term is also (incorrectly) used to refer to the current day of the year. — Keith Thompson, Jun 29 '15 at 18:00
I absolutely mean what you say by Julian. It is not 'YYYYDDD' like the other comment. So, how could I do it? — Yusuf Göker, Jun 30 '15 at 07:27
@tepples Please do not do tag only edits until you have the reputation to make non suggested edits. — NathanOliver, Jul 08 '15 at 17:04
@tepples It is part of the burninate procedure: http://meta.stackexchange.com/questions/239190/when-to-burninate — NathanOliver, Jul 08 '15 at 18:51
@NathanOliver Thanks. For my remaining questions, let's [get a room](https://chat.stackoverflow.com/rooms/82747/room-for-tepples-and-nathanoliver). — Damian Yerrick, Jul 08 '15 at 21:18

score 1 · Accepted Answer · answered Jun 30 '15 at 16:59

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)

score 0 · Answer 2 · answered Jun 29 '15 at 17:26

0

You need to use the DDD (day of year) date identifier in TO_CHAR.

answered Jun 29 '15 at 17:26

Mr. Llama

Thank you for comment but It is not what I meant. It is on the upper answers. – Yusuf Göker Jun 30 '15 at 07:29

2 Answers2