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.
My data looks like this in the table (these columns are all
What I’d like to get is a more common ISO datetime format of:
(On a side note, ordinal date representations like
YYYY-DDD are actually part of ISO 8601.)1
DATEADD to the Rescue
Luckily for me, MS-SQL has the function
DATEADD available 2.
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
minute and it will return a
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]