Date Issue

jmarin

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.  

Maskell_Rascal

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