10

how can I select Julian day of year in Oracle database?

I tried: select to_char(sysdate, 'J') from dual; Which gives me the number of days since January 1, 4712 BC. But I would need the number of days since 1.1. of current year.

Prmejc
  • 155
  • 1
  • 2
  • 13

4 Answers4

15

If you check the TO_CHAR (datetime) documentation you get a link to "Format Models" with a comprehensive list of available formats. I guess you want this:

DDD Day of year (1-366)

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • Does this also work for Julian calendar? So is Julian day of year the same as Gregorian day of year? – Prmejc Jul 16 '14 at 08:34
  • 1
    Do you want julian day (commonly used in factories for e.g. lot codes) or Julian calendar (the calendar obsoleted by Pope Gregory)? They're entirely different things. – Álvaro González Jul 16 '14 at 08:49
  • Yes it will be used for lot code and I found that julian day is the same as day of year by Gregorian calendar. So your answer is correct. Thank you very much :) – Prmejc Jul 16 '14 at 08:53
  • Day of year is probably the same concept in any calendar (Julian, Gregorian, Chinese, Roman...) because it's a simple count: 1, 2, 3... – Álvaro González Jul 16 '14 at 08:59
5
SELECT TO_CHAR(SYSDATE, 'DDD') from DUAL;
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Jeff Mergler
  • 1,384
  • 20
  • 27
4

One way would be to use:

select sysdate - trunc(sysdate,'yyyy') from dual

'Trunc' cuts everything except the year and returns 01/01/2014, subtracted by the sysdate returns numbers of days since 1st of january.

Christian
  • 227
  • 2
  • 17
  • 1
    Yes, but not in Julian calendar? Or does it? If this is the case I can just use: select to_char(sysdate, 'DDD') from dual; – Prmejc Jul 16 '14 at 08:13
  • 1
    You should add `+1` to that expression as yours starts counting days at `0` – Lukas Eder Aug 22 '18 at 11:33
1

Use sql select trunc(sysdate)+1 - trunc(sysdate,'yyyy') from dual. you will get an even number

Akin Okegbile
  • 1,108
  • 19
  • 36
theacb
  • 41
  • 7