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

user15252
user15252 Member
in Magic ETL

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.

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited August 2023 Answer ✓

    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

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited August 2023 Answer ✓

    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

    If I solved your problem, please select "yes" above

  • user15252
    user15252 Member

    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 :

    image.png
  • ColemenWilson
    ColemenWilson Coach

    Try it in a beastmode

    If I solved your problem, please select "yes" above

  • user15252
    user15252 Member

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

  • user15252
    user15252 Member

    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

Categories