HQL (HIVE) date_format adding an extra year after Dec 25th 2021

Question

I am not sure if this is just on my local instance or if it is a fundamental problem but for me in hive date_format jumps a year after December 25th 2021.

Select date_format('2021-12-25','YYYY-MM-dd') as Correct2021
        ,date_format('2021-12-26','YYYY-MM-dd') as Wrong2022
        ,date_format(date_add(current_date,51),'YYYY-MM-dd') as Correct2021b
        ,date_format(date_add(current_date,52),'YYYY-MM-dd') as Wrong2022b

The code above for me returns

correct2021	wrong2022	correct2021b	wrong2022b
2021-12-25	2022-12-26	2021-12-25	2022-12-26

If running on a different date then 11/4/2021 you would need to adjust the date adds.

score 1 · Answer 1 · answered Nov 04 '21 at 15:32

1

Turns out this is a java issue with 'Y' vs 'y'.

The issue is described here: Y returns 2012 while y returns 2011 in SimpleDateFormat

answered Nov 04 '21 at 15:32

Urza5589

139
1
9

score 1 · Accepted Answer · answered Nov 04 '21 at 15:33

Y pattern is a week year - a year which some week belongs to.

y - is what you need

Select date_format('2021-12-25','yyyy-MM-dd') as Correct2021
        ,date_format('2021-12-26','yyyy-MM-dd') as Wrong2022
        ,date_format(date_add(current_date,51),'yyyy-MM-dd') as Correct2021b
        ,date_format(date_add(current_date,52),'yyyy-MM-dd') as Wrong2022b

See also https://stackoverflow.com/a/69840917/2700344

HQL (HIVE) date_format adding an extra year after Dec 25th 2021

2 Answers2