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 1^st 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.

The Data

My data looks like this in the table (these columns are all smallint types):

What I’d like to get is a more common ISO datetime format of:

YYYY-MM-DD HH:MM:SS

(On a side note, ordinal date representations like YYYY-DDD are actually part of ISO 8601.)¹

DATEADD to the Rescue

Luckily for me, MS-SQL has the function DATEADD available ².

The syntax for this handy function is:

DATEADD (datepart, number, date)

Which allows me to easily add some arbitrary value to datepart which could be year, month, day, hour, or minute and it will return a datetime. Perfect!

This means I can continue to nest DATEADD calls as the date parameter and build up an actual datetime object by just adding minutes, then hours, then ordinal days to the first of the year (which I already know).

The nice thing about this approach is the automatic handling of leap years (you should have seen the code previously written to account for this…).

Now I can set a known date as a base and add days/time to it. Since I know the year easily enough I can generate a valid datetime for the first of the year at 00 hours and 00 minutes.

The final magic:

select yeardata, jday, timedata,
dateadd(day, jday,
            dateadd(hour, timedata/100,
                dateadd(minute, cast(right(timedata,2) as smallint),
                    convert( varchar(4), yeardata) ) ) ) as datething
FROM [Monitoring_Data].[dbo].[628_2016_met_min]