1

At my organization, people have been entering unknown dates as "11/11/1111". When I run a query with the date field, the value shows up as "11/04/1111". However, if I format it as to_char(datefield,'mm/dd/yyyy'), it correctly shows up as "11/11/1111". Can anyone explain why this is? I assume it has something to do with Julian vs Gregorian dates, but I can't find any information about it.

5
  • FWIW, in the Oracle Developer NLS settings, the date format is "MM/DD/YYYY".
    – Bernie L
    Oct 4, 2021 at 15:03
  • Also making me suspect this is client-side, if I add to the query CASE WHEN datefield <> to_char(datefield,'mm/dd/yyyy') THEN 1 END, it returns null, so the server sees them as equal.
    – Bernie L
    Oct 4, 2021 at 15:05
  • I can simplify this issue. If I run SELECT to_date('11/11/1111','mm/dd/yyyy') FROM DUAL, it returns a value of "11/04/1111".
    – Bernie L
    Oct 4, 2021 at 19:32
  • I just tried SELECT to_date('11/11/1111','mm/dd/yyyy') FROM DUAL at SQL Fiddle, and THERE it returns "1111-11-18T00:00:00Z" -- 7 days later, instead of 7 days earlier.
    – Bernie L
    Oct 7, 2021 at 1:56
  • I can confirm that the issue is related to the Julian vs. Gregorian calendar. In the Gregorian calendar, 10/4/1582 was followed by 10/15/1582. In Oracle, to_date(10/15/1582) returns 10/15/1582, to_date(10/14/1582) to 10/05/1582 ALSO return 10/15/1582 (because, sure, those dates don't exist). Then, to_date(10/04/1582) returns 9/24/1582. Dates prior to that are offset by 10; the number changes as leap day discrepancies arise. So the date inputted is Gregorian and the date displayed is Julian. I still don't know why it's displaying a Julian date given the NLS settings.
    – Bernie L
    Oct 27, 2021 at 14:37

1 Answer 1

-3

Interestingly, I don't see this in SQL Server (2019).

select
    [mydata]
    , cast([mydata] as varchar(20))
    , dateadd(dd, 1, [mydata])
from (
    select cast('11/11/1111' as date) as [mydata]
) as L1

Results

1
  • The question relates to Oracle not SQL Server
    – armitage
    Oct 6, 2021 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.