Using Date Diff in Big Query

Question

I have the below statements in BQ using Standard SQL. I have been asked to convert to Legacy to use with a 3rd party app.. I am struggling with the Date_Diff function. Any help would be greatly appreciated

1 - Select DATE_ADD(date([Client_StartDate]), interval - DATE_DIFF(DATE([Client_StartDate]), current_date, year) year) As AnniversaryDate

2 - Select DATE_DIFF(current_date, DATE([Client_StartDate]),month) as MonthsSinceStart

What is the third-party app that doesn't support standard SQL? Can you just use the #standardSQL comment at the top of your query with the app? — Elliott Brossard
I have no idea, Im just doing as I'm told by my CTO. I think its more that the app has other things being fed in that uses legacy so switching between the two not viable at this time — Glyn Bury
There's no reason that you can't use both, though...many other BigQuery users run a mix of legacy and standard SQL queries while making the migration toward standard SQL. It's up to you/your CTO, of course, but I would really just suggest using standard SQL instead of investing time into learning the quirks of legacy SQL. — Elliott Brossard
I understand and have conveyed this very same thought but for now a legacy version of this is needed until I can convince otherwise. Maybe showing this query is quite hard to convert might help my cause! — Glyn Bury

Elliott Brossard Elliott Brossard · Accepted Answer · 2017-05-05T12:35:36

Legacy SQL:

For the anniversary this year of the start date:

#legacySQL
SELECT
  DATE_ADD(
    Client_StartDate,
    YEAR(CURRENT_TIMESTAMP()) - YEAR(Client_StartDate),
    "YEAR") AS AnniversaryDate
FROM (SELECT TIMESTAMP('2015-02-23') AS Client_StartDate);

For the number of months since the start date:

#legacySQL
SELECT
  (YEAR(CURRENT_TIMESTAMP()) - YEAR(Client_StartDate)) * 12 +
    (MONTH(CURRENT_TIMESTAMP()) - MONTH(Client_StartDate)) AS MonthsSinceStart
FROM (SELECT TIMESTAMP('2015-02-23') AS Client_StartDate);

Standard SQL:

For the anniversary this year of the start date (assumed to be a timestamp):

#standardSQL
SELECT
  DATE(EXTRACT(YEAR FROM CURRENT_DATE()),
       EXTRACT(MONTH FROM Client_StartDate),
       EXTRACT(DAY FROM Client_StartDate)) AS AnniversaryDate
FROM (SELECT TIMESTAMP '2015-02-23' AS Client_StartDate);

For the number of months since the start date (assumed to be a timestamp):

#standardSQL
SELECT
  DATE_DIFF(
    CURRENT_DATE(),
    EXTRACT(DATE FROM Client_StartDate), MONTH) AS MonthsSinceStart
FROM (SELECT TIMESTAMP '2015-02-23' AS Client_StartDate);

One thing about the legacy SQL queries is that they operate on TIMESTAMP rather than the DATE type (DATE has limited functionality with legacy SQL). DATE is a nice type to work with in standard SQL because it's a logical point in time rather than a physical one and isn't tied to a timezone. You can read more about it in the data types reference.

Using Date Diff in Big Query

1 Answers