SQL Presto - Week function to star on a Sunday

Question

I'm trying to extract the week number from a date, and I want the week to be counted from Sunday to Saturday. This is what I currently have, but I can't seem to find any solution for this is SQL Presto.

SELECT WEEK(date) AS weeknum

Can this be solved?

Thank you!

Answer 1

One method is:

select week(date + interval '1 day') - interval '1 day'

Note: This may not work on the last day of the year.

Alternatively you can use the MySQL-like functions:

select date_format(date, '%V')

This has the week starting on Sunday.