What is the formula to convert Julian dates (format Cyyddd) to regular date - not datetime

user15252 · 2023-08-14T18:30:29+00:00

There was an error rendering this rich post.

I would like to be able to convert Julian dates with the above format to a regular calendar date. I don't need datetime. I can see a post for Julian yyddd, but not with Century and that only also includes a time stamp.

Find more posts tagged with

Accepted answers

ColemenWilson

DATE_FORMAT(`Julian`,'%Y-%m-%d')

I tested it with this data and it is working, test is the output of the above beastmode:

Screen Shot 2023-08-14 at 12.42.23 PM.png

All comments

ColemenWilson

DATE_FORMAT(`Julian`,'%Y-%m-%d')

I tested it with this data and it is working, test is the output of the above beastmode:

user15252

I assume I replace 'julian' with the julian date field name. I tried that and it did not work - Here is the error - which makes sense :

ColemenWilson

Try it in a beastmode

user15252

I want to do it in magic ETL2 - to work with it in my etl

user15252

This is the formula for julian yyyyddd —- date(dateadd(day,"Julian_date"% 1000,dateadd(year,(a."Julian_date"/1000),convert(datetime,'01/01/1900')))-1)

It works in ETL2. I would assume there is something similar with the Century format