15

In Bigquery's legacy SQL, I can get the start of week for a date by using

SELECT DATE((UTC_USEC_TO_WEEK(TIMESTAMP_TO_USEC(TIMESTAMP('2017-04-13 20:58:06 UTC')), 0)))

which returns 2017-04-09.

Is there a way to do this in BigQuery's standard SQL? There doesn't seem to be any equivalents for UTC_USEC_TO_WEEK and UTC_USEC_TO_MONTH.

Nigel Ng
  • 543
  • 1
  • 7
  • 21
  • Although there is a SQL Standard, SQL DB vendors generally have variations in how they handle dates. What specific SQL platform are you looking for? – Wes H Apr 13 '17 at 13:44
  • This might help you http://stackoverflow.com/questions/10007861/getting-the-starting-and-ending-date-of-week-in-sql-server – Shashidanand Jha Apr 13 '17 at 13:46
  • @WesH I'm using Google's Bigquery. I'm not sure which exact flavour of SQL they're using – Nigel Ng Apr 13 '17 at 13:49

2 Answers2

19

It looks like BigQuery has a function named TIMESTAMP_TRUNC which may do what you want. It is referenced as the replacement for UTC_USEC_TO_DAY(t) in LegacySQL when used with a Day datepart. It also accepts Week and Month as a parameter which may meet your requirements.

TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', WEEK, 'UTC')

Here is the page for migrating from Legacy to Standard sql

Wes H
  • 4,186
  • 2
  • 13
  • 24
16

This is better option that works now:

select DATE_TRUNC(date( '2008-12-25 15:30:00'), month)
Yerachmiel Elimelech Weiss
  • 210
  • 2
  • 8
  • 2
    Old topic but it still shows up high in search results. We can now use `LAST_DAY(date)` for last day of the month – Moseleyi Mar 11 '22 at 14:22