JDE Julian Dates in Databse Formula

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?

In Database

Database Connection

In-Database

Accepted answers

Jean-Balteryx

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 !

All comments

Jean-Balteryx

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

Isn't a 0 missing from 19000000 ?

EDIT : I misread it !

Jean-Balteryx

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

Alayna

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?