Converting an unknown six digit date format, possibly some variant of Julian

Question

I recently got access to a legacy database where all dates are stored in an unfamiliar format I'm unable to translate, my initial research makes me think this is a Julian date type, but it seems to be a bit off?

Thankfully there's one date column that has a normal counterpart to it, but there are a lot of other dates where only the six digit code exists.

Any ideas what these dates are, or more importantly, how to convert between these two formats?

EX:

LEGACY/NORMAL
152409/2018-04-13
152413/2018-04-17
152427/2018-05-01

It's an ancient MS-SQL database tied to an even more ancient COBOL program if that's relevant information.

Answer 1

It could be the number of Days since 1600-12-31

i.e. 1-Jan-1601 = 1 etc.

Would like to see dates from a different year to confirm

---

Cobol Date functions

As SaggingRuffus pointed out. Many dialects of Cobol have functions that convert dates To/From Days since 31-Dec-1600 These functions include:

INTEGER-OF-DATE converts YYYYMMDD date to Days-since 31-12-1600
INTEGER-OF-DAY  converts YYYYDDD  date to Days-since 31-12-1600

DATE-TO-INTEGER converts Days since 31-12-1600 to YYYMMDD
DAY-OF-INTEGER  converts Days since 31-12-1600 to YYYDDD

---

How I came to the Answer

I noticed that:

• 152413 - 152409 = 4 and 2018-04-17 is 4 days after 2018-04-13
• 152427 - 152413 = 14 and 2018-05-01 is 14 days after 2018-04-17

It was than a matter of doing the Date calculation which gives 31-dec-1600. I also knew there where date formats where the date was stored as the number of days from 1600/1601. A date in a different year would confirm the format

Answer 2

Adding to Bruce Martin's answer:

Even ancient SQL Server support DATEADD/DATEDIFF, you just have to modify the start date to 1900 instead of 1600 using the constant 109208 (the number of dates inbetween).

dateadd(day, (legacydatecol- 109208), 0)     as legacy2date
datediff(day, '19000101', datecol ) + 109208 as date2legacy