SQL - Julien Date (CYYDDD) to date

Question

Unfortunately, this is my first approach with SQL! I am creating with the following code a query between an oracle DB and Excel (Power Query).

select "$Table"."Order" as "Order",
    "$Table"."NR" as "Nr",
    "$Table"."JDDATE" as "JDDATE"
from "POOLDB2"."3112" "$Table"
WHERE   "Key" >118001
    AND "CodeAA" = 1

This code works!

Now I want to format the Julian Date (CYYDDD) - for example 118001 for the 01.01.2019 - to a normal date format. Does anyone know, how to implement this into the code above? Maybe something like :

select "$Table"."Order" as "Order",
    "$Table"."NR" as "Nr",
    DATEADD(DAY, JDDATE % 1000 - 1, DATEADD(year, JDDATE/1000, 0))
    "$Table"."JDDATE" as "JDDATE"
from "POOLDB2"."3112" "$Table"
WHERE   "Key" >118001
    AND "CodeAA" = 1

Best regards

Possible duplicate of [SQL Server : convert Julian Date to YYYY-MM-DD](https://stackoverflow.com/questions/33550260/sql-server-convert-julian-date-to-yyyy-mm-dd) — Joakim Danielson, Dec 12 '18 at 13:04
That is definitely **not** a Julian Date. Julian Date is defined as *the number of days since January 1st, 4713 BCE at noon*. As such, the JD difference between the same day of two adjacent years is 365 or 366, while in the CYYDDD format that would be always 1000. What you have there is an **ordinal date** (https://en.wikipedia.org/wiki/Ordinal_date ). — LSerni, Dec 12 '18 at 13:32
Are you sure 118001 is supposed to be 2019-01-01 and not 2018-01-01? — Alex Poole, Dec 12 '18 at 16:04
Yeah, my fault. 118001 is the first january 2018. I am sorry — , Dec 12 '18 at 16:08

GMB · Accepted Answer · 2018-12-12T15:30:57.573

2

There are many different formats for Julian Date... In your use case, this should do it :

with t as (select 118001 jd from dual)
select to_char( to_date(to_char(1901 + floor(jd / 1000)),'YYYY') + mod(jd,1000) - 1, 'dd.mm.yyyy' ) from t

Yields : 01.01.2019

edited Dec 12 '18 at 15:30

answered Dec 12 '18 at 13:28

GMB

216,147
25
84
135

Is this also working with oracle? How can I implement this in the code above? – Dec 12 '18 at 14:16
My answer is indeed for Oracle, because you mentionned it in your question (I also tagged your question "oracle"). It will probably not work in other RDBMS. – GMB Dec 12 '18 at 14:17
Thanks! It works now. But why is 118001 = 01.12.2019? It should be 01.01.2019, right? How can I adjust the code to get this? (DD.MM.YYYY) – Dec 12 '18 at 15:29
@joshua1990 yes my bad that was a typo, fixed it. Also adapted the query to match your expected output format. – GMB Dec 12 '18 at 15:31
I am sorry, but I still get for 118019 the 19.12.2019 (dd.mm.yyyy). But it should be the 19.01.2019. (EU-Standard). This is your code: to_char(to_date(to_char(1900 + floor("JDDATE" / 1000)), 'YYYY') + mod("JDDATE", 1000) -1, 'dd.mm.yyyy') as "Date", – Dec 12 '18 at 15:41
The problem is that you end up doing `to_date('2019', 'YYYY')`, and as there is no month supplied, Oracle defaults to the current month - i.e. December - rather than the first month of the year, generating 2019-12-01. If you want January then you have to specify that. – Alex Poole Dec 12 '18 at 15:45
I have already changed the 1901 to 1900, see code above. Is this what do you mean? – Dec 12 '18 at 15:47

Derviş Kayımbaşıoğlu · Answer 2 · 2018-12-12T14:39:10.197

2

For Oracle,

select to_char(sysdate,'J') from dual; --To Julian Date
select to_date(2456143,'J') from dual; --To Normal Date

must work.

Edit: Sorry I didn't see oracle tag.

Edit: For the requested behavior by OP

select to_date(to_char(1901 + floor(118001 / 1000)),'YYYY') from dual;

edited Dec 12 '18 at 14:39

answered Dec 12 '18 at 13:31

Derviş Kayımbaşıoğlu

28,492
4
50
72

Joshua stated in his question that he is using Oracle, not SQL Server – Dec 12 '18 at 13:33
Unfortunately this is not working. I think "@" is not appropriate for oracle. I am sorry – Dec 12 '18 at 14:15
please check my answer again – Derviş Kayımbaşıoğlu Dec 12 '18 at 14:18
Given `118001`, this returns `26/01/1990` - Not what the OP requested – GMB Dec 12 '18 at 14:24

Alex Poole · Answer 3 · 2018-12-12T16:34:13.200

You can use the 118001 value you have, split into separate year and day sections, by adding to the nominal starting date 1900-01-01 (based on your comment that 118001 is actually 2018-01-01, not 2019-01-01):

select date '1900-01-01'
  + floor(118001 / 1000) * interval '1' year
  + (mod(118001, 1000) - 1) * interval '1' day
from dual;

DATE'1900-
----------
2018-01-01

or by startng the fixed date a day earlier you can remove the explicit -1:

select date '1899-12-31'
  + floor(118019 / 1000) * interval '1' year
  + mod(118019, 1000) * interval '1' day
from dual;

DATE'1899-
----------
2018-01-19

This avoids having to build up a longer string to convert to a date, though you could do that (modifying @GMB's approach) as:

select to_date(to_char(1900 + floor(118001 / 1000)) || '-01-01', 'YYYY-MM-DD')
  + (mod(118001, 1000) - 1)
from dual;

You need to specify the month, at least, in the to_date() call as Oracle defaults to the current month if that is not supplied. That behaviour is tucked away in the documentation:

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.

The first part of that is fairly well known and makes sense ; the second part is a bit less obvious, and doesn't make it clear that it applies to partial dates too - so ifyou don't supply a year then the current year is used; if you don't supply a month then the current month is used; but if you don't supply a day then the 1st is used.

You can see what it's doing with some test conversions:

select to_date('2018-12-25', 'YYYY-MM-DD') as demo_a,
  to_date('12:34:56', 'HH24:MI:SS') as demo_b,
  to_date('2019', 'YYYY') as demo_c,
  to_date('07-04', 'MM-DD') as demo_d,
  to_date('2019-01', 'YYYY-MM') as demo_e
from dual;

DEMO_A              DEMO_B              DEMO_C              DEMO_D              DEMO_E             
------------------- ------------------- ------------------- ------------------- -------------------
2018-12-25 00:00:00 2018-12-01 12:34:56 2019-12-01 00:00:00 2018-07-04 00:00:00 2019-01-01 00:00:00

Yeah, the first approach works perfect! Thanks a lot! How can I get just a date format, without time? — , Dec 12 '18 at 16:14
You get a date object, which always has a time component, even if it's midnight. That doesn't have any intrinsic human-readable format though. It's up to your client/application how to display that. In my demo I've relied on `NLS_DATE_FORMAT`, but you can use `to_char()` to convert a date to a string in a specific format if you don't want the client/app to handle it. — Alex Poole, Dec 12 '18 at 16:23

SQL - Julien Date (CYYDDD) to date

3 Answers3

Linked