cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Ericsson
New Contributor II

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.result

2 REPLIES 2

Hubert-Dudek
Esteemed Contributor III

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
New Contributor III

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group