JDE Julian Dates in Databse Formula

[Alayna]

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?

[Jean-Balteryx · Accepted solution]

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?

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

[Alayna]

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

[Alayna]

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]

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

[Alayna]

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]

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