2

I've heard the UTC_USEC_TO_WEEK() function in legacy sql allows you to set the start week to monday (and not sunday which is the default). Basically, I want the below date queried to return 1 and not 2.

select week('2018-01-07') -- returns 2

I've seen how to do this in standard sql but I'm querying a legacy sql view so I can't switch to standard sql.

How do I go about using UTC_USEC_TO_WEEK to get monday as the week start?

Documentation isn't much help and can't find anything online. Any help with this would be much appreciated!

AK91
  • 671
  • 2
  • 13
  • 35

2 Answers2

0

Just move one day back with DATE_ADD() and then apply WEEK() 

#legacySQL
SELECT WEEK(DATE_ADD('2018-01-07', -1, "DAY"))
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • I should have mentioned that I'm aggregating and grouping by week as well (however that should ideally be raised as a separate question). All in all, you have answered the question I posted! For that, I thank you @Mikhail Berlyant! – AK91 Feb 18 '18 at 08:56
0

Essentially was looking for this week(utc_usec_to_week(current_date(),1)) - 1, so this would return the previous week with Monday as the start of the week.

AK91
  • 671
  • 2
  • 13
  • 35