Examples of working with Dates in Google BigQuery

CURRENT_DATE() as Current_Date, -- returns todays date

DATE(2024,01,24) as Constructed_Date, -- constructs the date from Year, Month, Day

LAST_DAY('2008-12-25') AS Last_Date, -- returns 2008-12-25 the last date

DATE_ADD(DATE "2024-01-24", INTERVAL 1 DAY), -- 1 day later

DATE_SUB(DATE "2024-01-24", INTERVAL 1 DAY), -- 1 day before

SELECT

CURRENT_DATE() as today, -- returns today

FORMAT_DATE('%d/%m/%Y', CURRENT_DATE()) AS formatteday, -- UK format

PARSE_DATE('%d-%m-%Y', '01-05-2024') as passstringdate, -- convert string to date object

FORMAT_DATE('%d/%m/%Y', PARSE_DATE('%d-%m-%Y', '01-05-2024')) AS formatedpassstring -- convert string to date and then format

--CURRENT_DATE() is 15/05/2024

SELECT

EXTRACT(DAY from CURRENT_DATE()) as day, -- 15/05/2024

EXTRACT(DAYOFWEEK from CURRENT_DATE()) as DayOfWeek, -- 4

EXTRACT(WEEK from CURRENT_DATE()) as Week, --19

EXTRACT(WEEK(MONDAY) from CURRENT_DATE()) as WeekStartingMonday, -- 20

EXTRACT(ISOWEEK from CURRENT_DATE()) as WeekStartingMonday2, -- 20

EXTRACT(MONTH from CURRENT_DATE()) as  Mth, -- 5

EXTRACT(QUARTER from CURRENT_DATE()) as Qtr, -- 2

EXTRACT(YEAR from CURRENT_DATE()) as Yr, -- 2024

EXTRACT(ISOYEAR from CURRENT_DATE()) as YrWkStartMon -- 2024

SELECT DATE_DIFF(DATE '2024-01-25', DATE '2024-01-02', DAY) AS days_diff -- returns 23 (note format end_date, start_date)

SELECT *
FROM `bigquery-public-data.google_trends.top_terms`
WHERE rank = 1
--Date Constructor
AND refresh_date >= DATE(2024, 01, 01)
LIMIT 10

SELECT *

FROM `bigquery-public-data.google_trends.top_terms`

WHERE rank = 1
-- DATE_SUB subtracts from given date

AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)

-- Limit result to 10 (works like TOP)

LIMIT 10