Jde julian date to calender date in pyspark

Question

I have dataframe df with column name ILDGL which record date in jde julian date format. I tried to convert that julian date into calender date and store in column ILDGL_Normal But I am not sucessful.

df = df.withColumn("ILDGL_Normal", to_date(concat(lit("20"), col("ILDGL")), "yyMMdd"))

Julian date 123002 mean 2023-01-02, 000001 mean 1900-01-01. How can i convert jde enterprise julian date into normal date format of YYYY-MM-DD?

Does this answer your question? [Pyspark : Convert Julian Date to Calendar date](https://stackoverflow.com/questions/72278968/pyspark-convert-julian-date-to-calendar-date) — Ronak Jain, Feb 07 '23 at 11:15
nope as that method is converting 123002 to null also that date is diffrent than mine — Arpan Ghimire, Feb 07 '23 at 11:37
@MohanaBC there are diffrent kind of julian date and the one i m talking is from jde julian date — Arpan Ghimire, Feb 07 '23 at 16:11

score 0 · Answer 1 · answered Feb 07 '23 at 19:23

The JDE Julian date format is CYYDDD

C - Century, Y - Year, D -Day of year

We can ignore Century and convert date to yyyy-MM-dd using to_date function. After that add years by checking centruy.

spark.conf.set("spark.sql.legacy.timeParserPolicy", "LEGACY")

df = spark.createDataFrame([('123002',), ('201002',), ('301002',)], ['jde_julian_date'])

df.withColumn("std_date",
              add_months(to_date(substring("jde_julian_date", 2, 5), 'yyDDD'),
                         when(substring("jde_julian_date", 0, 1) > 1,
                              (substring("jde_julian_date", 0, 1) - 1) * 100 * 12).
                         otherwise(0))).show()

+---------------+----------+
|jde_julian_date|  std_date|
+---------------+----------+
|         123002|2023-01-02|
|         201002|2101-01-02|
|         301002|2201-01-02|
+---------------+----------+

Jde julian date to calender date in pyspark

1 Answers1