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

JDE Julian Dates in Databse Formula

Alayna
8 - Asteroid

Hi, I'm pulling data from a large table in JDE and want to filter as much as possible, including the dates. The connection is AWS SQL. JDE uses 6 digit Julian dates: gldgj = 106,365. This comes in a fixed decimal format so Ive tried using the following formula in  the SQL expression: 

 

to_date(cast(cast(gldgj+1900000 as int) as varchar(7)),'YYYYDDD')

 

But the to_date function throws errors ("failed to tokenize string Y at offset 0") even though I'm converting it to a string first. Is there something wrong with the format or function argument? Or a better sql expression?

 

9 REPLIES 9
Jean-Balteryx
16 - Nebula
16 - Nebula

Hi @Alayna ,

 

I think the to_date function doesn't need a date format but rather it would be used to a date_parse function.

I think ALteryx way, don't know if it applies here !

Jean-Balteryx
16 - Nebula
16 - Nebula

Isn't a 0 missing from 19000000 ?

 

EDIT : I misread it !

Jean-Balteryx
16 - Nebula
16 - Nebula

If you sum 1900000 with 106,365 you get 2006365, which date does it represent ?

Alayna
8 - Asteroid

Hi Jean, thanks for the reply! Are you saying use date_parse instead of to_date? for some reason Alteryx isn't recognizing date_parse as a function - is this the right format?

date_parse(cast(cast(gldgj+1900000 as int) as varchar(7)),'yyyyddd')

Alayna
8 - Asteroid

This is supposed to be in a YYYYDDD format - 4 digit year (ex: 2006) and three digit day of the year (ex. 365)

Alayna
8 - Asteroid

Hi @Jean-Balteryx was that the correct way to type the function? Alteryx isn't recognizing date_parse... I wonder if it's not used in  Athena?

Jean-Balteryx
16 - Nebula
16 - Nebula

I may be wrong about date_parse. I don't know Athena so it may not exist! 

Alayna
8 - Asteroid

Actually @Jean-Balteryx u were right, I was using the wrong language for the input arguments. Thank you!

date_parse(cast(cast(gldgj+1900000 as int) as varchar(7)),'%Y%j')

 

Jean-Balteryx
16 - Nebula
16 - Nebula

You are welcome! I'm glad you sorted it out! 

Labels