Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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?

 

5 REPLIES 5
Jean-Balteryx
14 - Magnetar

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
14 - Magnetar

Isn't a 0 missing from 19000000 ?

 

EDIT : I misread it !

Jean-Balteryx
14 - Magnetar

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)

Labels