SDU Tools: Julian Day Number to Date in T-SQL (and reverse)

Working with dates and times in database systems and programming languages has always been “interesting”, at least interesting in the sense that the old Chinese curse meant when it said “may you live in interesting times”.

One of the curious variations though is the use of Julian day numbers. It’s a count of the number of days since the beginning of the Julian period: https://en.wikipedia.org/wiki/Julian_day

SQL Server doesn’t currently have a built-in function for converting to or from these so we added functions to our free SDU Tools for developers and DBAs. You can use the functions directly from our tool kit, or use them as examples of how to write these functions.

JulianDayNumberToDate and DateToJulianDayNumber are the two functions that allow you (in T-SQL) to easily change between the two. The range of values that is allowed is between 1721426 (‘00010101’) and 5373120 (‘99990101’).

You can see JulianDayNumberToDate in the image above, and this is DateToJulianDayNumber: