CODEWITHSUNDEEP
sqloracle-databasejulian-date
Prmejc
Prmejc

Reputation: 155

Oracle Julian day of year

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.

Upvotes: 10

Views: 24121

Answers (4)

theacb
theacb

Reputation: 31

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

Upvotes: 1

Jeff Mergler
Jeff Mergler

Reputation: 1532

SELECT TO_CHAR(SYSDATE, 'DDD') from DUAL;

Upvotes: 6

Álvaro González
Álvaro González

Reputation: 146500

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)

Upvotes: 16

Christian
Christian

Reputation: 227

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.

Upvotes: 4

Related Questions