1

I have a large data file containing a string type column 'YearMonthWeek' It contains values such as '20160101' for the first week of January 2016, or '20161040' for the 40th week of the year 2016 apparently falling in October.

Now, I want to convert these strings to actual dates so that every YearMonthWeek value is converted to, say the first day of that week. (Whether that ends up being Monday or Sunday I don't really care).

I tried the following query:

PARSE_TIMESTAMP('%Y%m%W', CAST(YearMonthWeek AS STRING)) AS datefield

(See this documentation for details) This runs without errors, but returns me the first day of the month for every single entry...

So for example '20160101' and '20160102' both get parsed as 2016-01-01 00:00:00 UTC.

Is this an issue with the PARSE_TIMESTAMP function, or am I missing something?

Tr1pzz
  • 31
  • 1
  • 5
  • Might be a Duplicate, or might just help you out https://stackoverflow.com/questions/30364141/mysql-convert-yearweek-to-date – RiggsFolly Jun 20 '17 at 13:54
  • This is weird. I just tried `select PARSE_TIMESTAMP('%Y%W', '201640') ` and this returns 2016-01-01. Perhaps something is wrong with `%W` parsing. – Gordon Linoff Jun 20 '17 at 13:55
  • 2
    Yea, you need to loose the unnecessary month part or the date – RiggsFolly Jun 20 '17 at 13:56
  • @RiggsFolly doesn't work. – Pentium10 Jun 20 '17 at 14:00
  • There's a relevant [feature request to support `%W`](https://issuetracker.google.com/issues/35906001). It most likely will only enable year and week but not year, month, and week, however. – Elliott Brossard Jun 20 '17 at 14:45
  • You have a low rate. Important on SO, you have to mark accepted answers by using the tick on the left of the posted answer, below the voting. This will increase your rate. See how this works by visinting this link: http://meta.stackoverflow.com/questions/5234/how-does-accepting-an-answer-work#5235 – Pentium10 Dec 18 '17 at 12:18

1 Answers1

4

Try doing something like 

DATE_ADD(date_expression, INTERVAL %W WEEK)

Static example:

SELECT
  DATE_ADD(
          DATE(PARSE_TIMESTAMP('%Y', SUBSTR(CAST('20161252' AS STRING),0,4))),
          INTERVAL (CAST(SUBSTR(CAST('20160102' AS STRING),7) AS INT64)) week) 
        AS datefield

-

Row datefield    
1   2016-01-15

You may add something as a margin to it, according to ISO 8601, the first week of the year is the one that contains January 4th. So you could have something like: 4 + 7*($week - 1)

Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • This question is for Google BigQuery. there it works. MySQL is different, it's not based on SQL2011 standard. – Pentium10 Jun 20 '17 at 14:05
  • My Apologise squire – RiggsFolly Jun 20 '17 at 14:09
  • This will work for dates in january, but you will fail for other months. The first part should only keep the year, so that the interval add starts from January 1st. – Fematich Jun 20 '17 at 14:17
  • 1
    Ended up using this one: `SELECT DATE_ADD( DATE(PARSE_TIMESTAMP('%Y', SUBSTR(CAST('20161252' AS STRING),0,4))), INTERVAL(CAST(SUBSTR(CAST('20161252' AS STRING),7) AS INT64)) week) AS datefield` Thx a lot! – Tr1pzz Jun 20 '17 at 14:23