Converting Ordinal (Julian) Dates in SQL
I was recently put in charge of some (Microsoft SQL) databases for monitoring meteorological (and hydrological) data down here in Mobile Bay. One of the fun things I’ve found is that it’s apparently common to identify this data based on its ordinal day in the year. That is, a sequential count of the number of days since the first of that year.
(Not to be confused with the Julian date which is the number of days since 01 January 4713 BC Greenwich noon. Yeah, fun.)
For example, the first of the year can be written as
2021-001 while today, 11 July 2021 can be written as
2021-192. All the way up to 365 (or 366 for leap years).
This may be great for astronomical data but it’s a bit cumbersome when looking at data that has expected range values for a given season. At least for what an investigator’s expectations may be.
For instance, if I told you it was 90° F (32.2° C, 305.37 K) on August 1st you might think it’s normal (well, normal for southern Alabama). If I said it was 90° F on day 304 you might have a harder time noticing that it seemed unusually warm (for a Halloween day).
So I needed to generate a report in something more easily consumed by non-date nerds.
I also wanted to throw some of this data into something like Grafana for some nice visualizations, which is expecting a more familiar ISO-8601 datetime (
YYYY-MM-DD HH:MM:SS) when graphing data.
Because I’m a glutton for punishment I decided I wanted this as part of my SQL queries.