On Dates, Dating, and Cryptography

Time to geek out a bit. Ok, ok … it’s time to geek out a lot. Those of you who aren’t interested in programming, Julian or Gregorian calendars or Neal Stephenson should probably stop reading now.

I’m working on a project at work where I have to select a bunch of dates from a MS SQL database. At first I thought I could do this using a similar “SELECT” statement, some date variables, and the database’s two columns for “START_DATE” and “END_DATE”.

But alas, these date columns were defined as “VARCHAR” rather than “DATE”, meaning that MS SQL barfs if you try and compare the contents of the date columns to the actual date.

Very annoying.

But the self-same database does have the Julian start and end dates. It gave the year, followed by the number of days since the beginning of the year.

I have since come to realize that this is not the Julian date. I don’t know what it’s actually called, so I’ve taken to calling it the “quasi-julian” date. But more on that later.

After conferencing with a collegue, I decided to see if I could find something in PHP that would convert a date to the Julian date. I did a search on google for “PHP Julian Date Conversion” … and landed at an online dating service called “mega dating tips”. Yes, that’s right, a Web site offering advice on how to get a date.

After a long, frustrating morning of trying to trying to get data in and out of the database, this was just too much. I just sat back and laughed. And then laughed some more. And then I started searching again.

I found three pages of note:

As a result of all of this, I learned that what I was dealing with wasn’t really a Julian date, although that’s what the column name implied. A true Julian date is the number of days that have passed since 4713 B.C. (or B.C.E. for the politically correct). For example, 11/26/2003 is 2,452,970. That’s a hell of a lot of days.

At this point I realized that what I was working with wasn’t really a Julian date — it was just the number of days since the beginning of the year (with January 1 starting the count at zero). I initially tried to find a PHP function that would convert the current date to a “number of days” count, but gave up.

Instead, I decided to put the Julian date to work. I used the PHP function “gregoriantojd” to figure out the Julian day count for January 1, 2003. And then I used the same function to figure out the Julian count for the current day. I subtracted the current date from the start date, and got the current date count. Yippie! But it was one higher than the number I had in the database.

I scratched my head a bit and then realized that the “quasi-julian” date living in the database had started its count at 0, rather than 1. Subtracting one from the “start date” number gave me the value I needed.

A short amount of scripting later, and I was finally able to get all of the “November” dates out of the freaking database. The project isn’t complete, not by a longshot, but one of the biggest hurdles has now been cleared.


This little mis-adventure reminded me of why I love being a webmaster so much — you never know when you’re going to encounter some archaic bit of knowledge that still impacts on your day-to-day work. I mean hell, when I got up today I had no idea how much time I was going to be spending figuring out how to convert dates between Julian and Gregorian calendars. I couldn’t have even told you what a Julian calendar was!

It also calls to mind why I like Neal Stephenson’s work so much, particularly Cryptonomicon and Snow Crash. Because in those books you’ve always got this historical baggage that comes crashing back onto what you’re doing today. In one, you’ve got ancient cryptography systems. In the other, you’ve got ancient Babylonian texts. It’s just all too freaking cool … at least for a history-loving geek like me.