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

1 Like

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