Julian Date
Hi Experts,
I have connected to one database and I am getting date in Julian format. How can I convert them into normal date?
Thanks,
Ajeet Mahajan
Answers
-
HI @user18189
Which format are your Julian dates in? YYDDD or YYYYDDD?
Since Julian dates count the number of days and doesn't use months you'd need some sort of DATE_ADD function call to add the number of days.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
0
-
If you're doing this via a Magic ETL2.0 you can utilize these formulas in a single formula tile: (MAGIC ETL2.0 only - I've broken them down for easier reability). julian is your date field with your Julian date. This formula assumes you'll always have a 7 digit date.
JDAY:
RIGHT(`julian`, 3)
JYEAR:
LEFT(`julian`, 4)
GYEAR:
DATE_FORMAT(CONCAT(`JYEAR`, '-01-01'), 'YYYY-MM-DD')
GDATE:
DATE_ADD(`GYEAR`, (`JDAY` - 1))
Alternatively if you want to do this in a beast mode or want a condensed version for your formula tile you can do this:
DATE_ADD(DATE_FORMAT(CONCAT(LEFT(`julian`, 4), '-01-01'), 'YYYY-MM-DD'), (RIGHT(`julian`, 3) - 1))
To break down the logic. I'm splitting out the days and years, then calculating the first of the year. Once I have the first of the year I'll then add the specific number of days to the first of the year (minus 1 since we're 1 based).
If you don't have Magic ETL 2.0 (in beta) talk to your CSM or alternatively use magic 1.0 tiles to get the left and right values from your julian date, figure the first of the year for your julian date, add the days to that date.
Something like this should work (copy and paste into your magic ETL 1.0 dataflow)
{"contentType":"domo/dataflow-actions","data":[{"name":"String Operations","id":"b4919418-af27-4d33-ba57-5337b66ca10a","type":"StringCalculator","gui":{"x":456,"y":120},"dependsOn":["302bdd61-b1d8-41f9-b935-bfa893f48562"],"removeByDefault":false,"notes":[],"calculations":[{"calcType":"SUBSTR_RL","constantB":{"value":"4","type":"LONG"},"constantC":{"value":"LEFT","type":"STRING"},"fieldA":"julian","fieldName":"JYEAR"},{"calcType":"SUBSTR_RL","constantB":{"value":"3","type":"LONG"},"constantC":{"value":"RIGHT","type":"STRING"},"fieldA":"julian","fieldName":"JDAY"}]},{"name":"Combine Columns","id":"e7d3dcda-71ee-4b69-a75a-ddcefd7858d5","type":"ConcatFields","gui":{"x":672,"y":120},"dependsOn":["e21f76c7-59f8-49b3-aada-891e70fbe989"],"removeByDefault":false,"notes":[],"separator":"-","targetFieldName":"GSTART","removeSelectedFields":true,"fields":[{"name":"JYEAR"},{"name":"GMONTH"},{"name":"GDAY"}]},{"name":"Add Constants 1","id":"e21f76c7-59f8-49b3-aada-891e70fbe989","type":"Constant","gui":{"x":576,"y":120},"dependsOn":["b4919418-af27-4d33-ba57-5337b66ca10a"],"removeByDefault":false,"notes":[],"fields":[{"name":"GDAY","type":"STRING","value":"01"},{"name":"GMONTH","type":"STRING","value":"01"}]},{"name":"Set Column Type","id":"5cf87171-de0e-4efd-a6bb-ac6ea4af627c","type":"Metadata","gui":{"x":768,"y":120},"dependsOn":["e7d3dcda-71ee-4b69-a75a-ddcefd7858d5"],"removeByDefault":false,"notes":[],"fields":[{"name":"GSTART","type":"DATE","dateFormat":"YEAR_FIRST"},{"name":"JDAY","type":"LONG"}]},{"name":"Date Operations","id":"f7af926d-8cbe-476b-aa27-0fb02863aaaf","type":"DateCalculator","gui":{"x":888,"y":120},"dependsOn":["5cf87171-de0e-4efd-a6bb-ac6ea4af627c"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Gregorian 1","calcType":"ADD_SUBTRACT_DATE","fieldA":"GSTART","fieldB":"JDAY","constantC":{"type":"STRING","value":"DAYS"}}]}]}
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
@GrantSmith Thanks for replying!
0 -
Did that solve your problem? If so can you accept the answer so it's easier for others to find the solution in the future? Thanks!
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
What would the formula be for Julian dates with century - Cyyddd?
0 -
@user15252 for that instead of LEFT(
julian
, 4) you'd to LEFT(julian
, 3), under the assumption you will have only single digits centuries, otherwise LEFT(julian
, LEN(julian)-3) and then add 1900 to that number, that will become your JYEAR, all other steps remain the same.0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive