How to convert a date to the correct year and half year in google bigquery?

3
votes

This gives me the year and quarter from any date:

CONCAT(STRING(YEAR(mydate)),'-Q',STRING(QUARTER(mydate))) as quarter

Result: 2016-Q2, 2016-Q3, 2016-Q4, 2017-Q1, etc

What should I write to get the half year? (2017-S1, 2017-S2, etc)

S1 (Semester 1) is between Jan 1st-Jun 30th; S2 (Semester 2) is between Jul 1st-Dec 31st.

Had no problem making dates into quarters and then connecting other tables data to the right quarter, but don't know how to do half years (i know the direct formula does not exist in sql). Thanks!

1
sqlgoogle-bigquery

1 Answers

3
votes

Try below (it is in Legacy SQL as I see you still using it) 

#legacySQL
SELECT 
  CONCAT(STRING(YEAR(mydate)),'-Q',STRING(QUARTER(mydate))) as quarter,
  CONCAT(STRING(YEAR(mydate)),'-S',STRING(IF(QUARTER(mydate) < 3, 1, 2))) as semester
FROM 
  (SELECT CURRENT_DATE() AS mydate),
  (SELECT '2016-02-02' AS mydate), 
  (SELECT '2016-09-02' AS mydate)

In case if you will be migrating to Standard SQL (which is highly recommended) - check respective version 

#standardSQL
WITH yourTable AS (
  SELECT CURRENT_DATE() AS mydate UNION ALL
  SELECT '2016-02-02' UNION ALL
  SELECT '2016-09-02' 
)
SELECT 
  mydate,
  CONCAT(FORMAT_DATE("%Y", mydate), '-Q', CAST(EXTRACT(QUARTER FROM mydate) AS STRING)) AS quarter,
  CONCAT(FORMAT_DATE("%Y", mydate), IF(EXTRACT(QUARTER FROM mydate) < 3, '-S1', '-S2')) AS semester
FROM yourTable