SQL week format issue its not showing result as 01(ww)

[Ericsson]

Hi Folks,

I've requirement to show the week number as ww format. Please see the below code

select weekofyear(date_add(to_date(current_date, 'yyyyMMdd'), +35)). also plz refre the screen shot for result.

[Hubert-Dudek]

date_add id adding days. Today is 1st December and +35 days it gives 5th January which is first week of 2022 so result 1 is correct as returned by weekofyear function.

If you need just add leading zero the best way is to format it in visualization tool like / BI.

If you need leading zero in spark it will be have to be the string:

SELECT lpad(weekofyear(date_add(current_date, 35)), 2, '0')

It could be nice that date object is just format (date_format) as week number WW/ww but in spark is not included in date formatting: https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html

[Lauri]

You can use lpad() to achieve the 'ww' format.