We have a database in which dates are stored in Julian format. We have tried the following:
1) Add input (AS400 DB)
2) Adding select (to change the data type of date field to string)
3) Adding formula to convert 7-digit Julian to regular date (DateTimeFormat(DateTimeParse([JULIANDATE],"%Y%j"), "%m/%d/%y"))
This solves part of the issue. It now returns the right date, however, in "string" format. When attempting to add another "Select" function to edit to "Date" format, it throws everything out of whack.
We need it to be in date format because we need to query a range of transactions; if not, the database is huge and it's extracting all historical data from inception (which takes forever)
Any simpler method you know of? Any help is appreciated.
Solved! Go to Solution.
Hi @jmarin ,
The only date format allowed as date in Alteryx is yyyy-mm-dd and you can accomplish that using this part of the function: DateTimeParse([JULIANDATE],"%Y%j") and using the select tool to change its data type to date.
If you need a different format, such as %m/%d/%y, it is only allowed as a string.
Best,
Fernando Vizcaino
Hi @jmarin
Try this formula:
DateTimeParse(ToString([Julian Date Field]),"%Y%j")
You can then set your field automatically to Date in the formula tool.
If this solves your issue please mark the answer as correct, if not let me know!
Thanks!
Phil
It didn't work 😕
It gives me some errors on the initial conversion, and even if I ignore those errors, the final outcome is giving me whacky dates (e.g. 1997-04-0)
Same here 😕
It gave me an error and then returns wacky dates with days being "0"
Hey @jmarin
Increase your field length. I'm guessing you have your field type set to a String and the field length is set to 9, so the output is working but its truncating it to fit to your selected field size.
Thank you! I'm obviously a newb, but appreciate it! It's working now!
Hi @Maskell_Rascal,
I was wondering if you knew what this new error was. I used the same query you helped me with but simply removed one of our filters to view the entire listing (not just a specific code) and now I received this:
Hi @jmarin
The error is being thrown due to values of '0' being present in your Julian Date field.
Thanks!
Phil