I often get asked how to convert a datetime into Julian Date format in T-SQL. People have differing opinions about what Julian means, but the one I got asked about most recently meant YYDDD, as often used by mainframe systems (I think this is Julian Date, as opposed to Julian Day which is the number of days since 4713BC). SQL Server doesn’t have a TO_JULIAN function, but we can make one easily enough.

Converting Between Unix Timestamp and Julian Date. Depending on your version of Origin, there are a couple of ways to convert between a Unix timestamp and a Julian Date: Beginning with Origin 2020b, you can use Origin's UnixTime( ) function to convert values in either direction. In this article you'll learn, how to convert a Julian date to a calendar date. What is Julian date? Julian date calendar was introduced by Julius Caesar. By a Julian date do you mean one in the form yyyyddd i.e. 2004147 would convert to 2004/05/26? I don't really understand your script but here is a standalone script which you may be able to adapt to your needs.

So we’re wanting to express a date as YYDDD, where YY is the two-digit form of the year, and DDD is the number of days since Dec 31st of the previous year (ie, the DDDth day of the year).

Using the DATEPART function can get each part. YY for the year, and DY for the day of the year. I’m going to use @date as a variable here, of type datetime. Using the date type in SQL 2008 would work just the same.

SELECT DATEPART(yy, @date), DATEPART(dy, @date)

However, to make sure that we have the year in two-digits only, we should convert this to a string and get the rightmost two characters.

SELECT RIGHT(CAST(DATEPART(yy, @date) AS char(4)),2)

We also need to pad the DDD with zeroes – which I’ll do by putting three zeroes in front of the number and getting the three rightmost characters.

SELECT RIGHT(‘000’ + CAST(DATEPART(dy, @date) AS varchar(3)),3)

Concatenating the YY and the DDD, we now have a TO_JULIAN function.

SELECT RIGHT(CAST(YEAR(@date) AS CHAR(4)),2) + RIGHT(‘000’ + CAST(DATEPART(dy, @date) AS varchar(3)),3)

Converting back again isn’t too hard – it’s just a matter of pulling the numbers out of the 5-character string. I’m going to assume we have a char(5) called @julian.

We need to split the string up first.

SELECT LEFT(@julian,2), RIGHT(@julian,3)

The first bit becomes the year easily enough

SELECT CONVERT(datetime, LEFT(@julian,2) + ‘0101’, 112)

The second half can be cast to a number, and then added back (subtracting one to get the maths right) using DATEADD.

SELECT DATEADD(day, CAST(RIGHT(@julian,3) AS int) – 1, CONVERT(datetime, LEFT(@julian,2) + ‘0101’, 112))

So now we have a FROM_JULIAN function:

SELECT DATEADD(day, CAST(RIGHT(@julian,3) AS int) – 1, CONVERT(datetime, LEFT(@julian,2) + ‘0101’, 112))

Bash Script Convert Julian Date To A Calendar Date

Easy stuff really, just a matter of thinking about what we mean by a particular format.

Converting date formats can be a tricky thing. Two of the more cryptic formats that come up in our support questions relate to Julian dates and Unix timestamps. Here's how you convert them in Strata:

Julian Dates

Bash Script Convert Julian Date To A Calendar Date Calendar

A Julian date is the number of days since January 1, 4713 BC GMT. To convert a Julian date to the current date, you can simply use DATE(0,0,0) + <number>, where <number> is the Julian date. For for example, if the number appears as “2454917″, the formula in Strata would be:

Convert Julian Date To Calendar Date In Unix

date(0,0,0) + 2454917 [the result would be March, 26, 2009]

Unix Timestamps

A unix timestamp is the number of seconds since 1970. To convert a unix timestamp to the current date, use DATE(<number>), where <number> is the number of milliseconds since 1970. If the timestamp is given in seconds, multiply by 1000: DATE(<number>*1000). So, for example, if you the number appears as “1237657172″, the formula in Strata would be:

date(1238088021 * 1000) [the result would be March, 26, 2009]

As a side note, sometimes one might mistake a Julian or Unix date for a different custom format. For instance, dates could be stored in the format YYDDD or YYYYDDD where YY/YYYY is the year, and DDD is the number of days since the beginning of that year. So 2001003 would be January 3, 2001

For further information on Strata date conversion, please see this help page.