Week Numbers (1-53)

Today the extract() function does not support extracting the ‘week number’ from a date field. As a workaround you can use a formula like this (may need some adjustments +/-1 for certain years, could add some ifElse logic to account for that). FYI this uses weeks starting Sun.

dateDiff(truncDate("WK", truncDate('YYYY', {order_date})), {order_date}, 'WK')+1


Hi Jesse, thanks for providing this solution! Is there a way to get the week to start on Monday? I have had some trouble in the past with trying to align Quicksight with data from Athena, which does not have support for weeks starting on Sunday.

Presto documentation
%V Week (0153), where Sunday is the first day of the week; used with %X
The following specifiers are not currently supported: %D %U %u %V %w %X

Nice, that helped a lot!

For the week starting on Monday:

    extract("WD", Date) = 1,
    dateDiff(truncDate("WK", truncDate('YYYY', Date)), Date, 'WK'),
    dateDiff(truncDate("WK", truncDate('YYYY', Date)), Date, 'WK')+1

This can be changed to any day of the week can be used as starting point.


It did not seem to work for me. It shows Dec 26, 2022 as Wk53 however it is Wk52. Any ideas? Am i missing something?

Depending on the year you may need to add/subtract 1 from the result. Stay tuned for a new function called WEEKNUM() soon though :wink: