Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Date Issue

jmarin
6 - Meteoroid

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.  

8 REPLIES 8
fmvizcaino
17 - Castor
17 - Castor

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

Maskell_Rascal
13 - Pulsar

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.

 

Maskell_Rascal_0-1617830474937.png

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Thanks!

Phil

 

jmarin
6 - Meteoroid

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)

Capture.PNG

jmarin
6 - Meteoroid

Same here 😕  

 

  It gave me an error and then returns wacky dates with days being "0"

Capture3.PNG

Capture2.PNG

Maskell_Rascal
13 - Pulsar

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. 

jmarin
6 - Meteoroid

Thank you! I'm obviously a newb, but appreciate it! It's working now!

jmarin
6 - Meteoroid

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:

 

Capture.PNG

 

 

Maskell_Rascal
13 - Pulsar

Hi @jmarin 

 

The error is being thrown due to values of '0' being present in your Julian Date field. 

 

Thanks!

Phil

Labels