Y2K.  It was all the rage back in the last third of the 1990’s.   I was part of a team that specialized in converting systems to handle those extra 2 bytes that constituted the century delineation.  Those where the days… well, those were sleepless days for those of us that converted big business systems so the world would keep humming along even after 01/01/2000.

I was happy to wave goodbye to Y2K.  I was through with it and I thought I would never have to deal with date issues ever again. All Dates were complete, with the appropriate ’20’ or ‘19’ in front of their two-digit year.  All was well in the database world.  Citizens were beginning to relax and starting to feel a little silly about the bunkers they built in their backyards.  It was done.  Over.  Amen.

Now, 15 years after Y2K fizzled out of the public’s consciousness, I found myself dealing with one of the date ‘gifts’ that Y2K left for developers the world over.  I found myself faced with the dreaded ‘Binarily Enhanced Julian Date!’  For those born around 1990, I don’t expect you to fully appreciate, or gasp with dismay, when reading about my encounter with this slippery eel of a date. 

Let’s take just a moment to talk about Julian Dates.  I realize that the information I am about to share is common knowledge, but for those Millennials among us, I’ll include a small bit of background about Julian Dates.

Originally, the Julian Day number 0, equated to noon on January 1, 4713 BC.   As we all know, nothing happened prior to 01/01/4713 BC and therefore it was the perfect choice for day 0. 

A Julian Date (JD), as originally designed, consisted of the number of days since day 0. For instance midnight on 12/10/2014 converts to the Julian Day Number of:  2457001.5.  This makes total sense to all of us who really understand that there is no need to keep track of dates that occurred when nothing interesting was happening. 

After quite a long time, people realized that 4713 BC was a very, very long time ago and there was no need to keep receipts from that time period.  To make date calculations more efficient; a Modified Julian Day Number was introduced. 

People also realized, that it was a little odd to start a date at noon and so they designated midnight the new start of a day.  It was decided that the Modified Julian Day Number (MJD) 0 would begin at midnight on November 16, 1858.  

Enter business machines (think mainframe and midrange).  With the emergence of corporations and the computer systems that kept their corporate blood flowing, the Julian Day Number took on a type of  ‘variable’ meaning.   The Commercial Julian Date (better known as ‘HUH???’) consists of the number of the day in whatever-year-you-want-it-to-be.  Business computers began storing their version of Julian dates in the following format: YYDDD.  For example, 87001 resolves to January 1, 1987.  The first two digits represent the year and the last three digits represent the day of the year.

It never occurred to anyone one at that time that someday the 1900’s would end and the 2000’s would arrive.  There was no need to define the year with a silly old century.  All dates began with 19 and always would  – Hah!

Time passes.  The year 2000 is approaching.  Now those oh-so-efficient two digit year designations are about to cause a global meltdown.

The Y2K bug was fixed.  The century bytes were added to every date.  Did I say ‘bytes’ with an ‘s’?  Well, not all systems were updated with a a19 or 20 century in front of the year.  There were some who opted to modify their dates using 1 and 0.  0 stood for 19 and 1 stood for 20.  In this format a Commercial Julian Date representing December 17, 2014, binarily enhanced, looks like this: 114351.  Awesome, huh?  This is the data type I had the distinct displeasure of meeting.

NOW, ON TO THE SQL SEVER SECTION OF OUR DRAMA.

I needed to convert dates formatted like 114351, to a standard date format. The select statement below did the job of converting invoice dates in this ‘binarily enhanced Julian Date’ format to a date that makes sense.

This statement returns two columns in the result set.  InvoiceDate contains the date in it’s original format and InvoiceDateNormal contains the date in a human friendly format.

SELECTInvoiceDate,invoiceDateNormal = (CASEWHEN SUBSTRING(CAST(invoiceDate AS VARCHAR), 1,1) = 1THEN DATEADD(day, SUBSTRING(CAST(invoiceDate AS VARCHAR), 4,3)  -1,'20' + SUBSTRING(CAST(invoiceDate AS VARCHAR), 2,2) +  '-01-01')WHEN SUBSTRING(CAST(invoiceDate AS VARCHAR), 1,1) <> 1THEN DATEADD(day, SUBSTRING(CAST(invoiceDate AS VARCHAR), 4,3)  -1,'19' + SUBSTRING(CAST(invoiceDate AS VARCHAR), 2,2) + '-01-01')END)FROM someTable WITH(nolock)

In the CASE statement, the first number in the date is evaluated to determine the century.  The example date begins with a 1, representing 20 as the century.    1 14351

WHEN SUBSTRING(CAST(invoiceDate AS VARCHAR), 1,1) = 1

DATEADD is used to add days to a starting date. The last three numbers in the value are isolated 114 351.  This is the day of the year number.  It is necessary to subtract 1 from the day number when calculating the date to enable proper date calculation.

THEN DATEADD(day,  SUBSTRING(CAST(invoiceDate AS VARCHAR), 4,3)  -1,

Now, we build the date from which we add the day of the year number.  This is the starting date. The first number (1) is replaced with the proper century.  In example the century is 20.

The numbers in positions two and three in our Julian date represent the year in our date:  1 14 351.  We concatenate the century, the year and ’01-01’ to build the date that is the first day in the year indicated by the year value.  In this example the starting date is January 1, 2014 (2014-01-01).

’20’ + SUBSTRING(CAST(invoiceDate AS VARCHAR), 2,2) +  ‘-01-01’)

The result set produced when running this query is shown below.