BigQuery datetime functions: syntax and tested examples
A reference to BigQuery's DATETIME functions with one tested example each: building a datetime, converting a timestamp with a time zone, adding and subtracting, differences, truncation, formatting and parsing. It also covers why SAFE.DATETIME fails on a string and what to use instead.
A DATETIME in BigQuery is a date and a clock time with no time zone: 2026-10-06 09:30:00, as read off a wall clock. This page lists the functions that build, change and format one. Every result was produced in BigQuery on 6 October 2026.
| To do this | Use | Result |
|---|---|---|
| Build from parts | DATETIME(2026, 10, 6, 9, 30, 0) |
2026-10-06T09:30:00 |
| Timestamp to local time | DATETIME(ts, 'Asia/Tokyo') |
2026-10-07T08:30:00 |
| Add or subtract | DATETIME_ADD, DATETIME_SUB |
2026-10-06T11:00:00 |
| Difference | DATETIME_DIFF |
11 |
| Round down | DATETIME_TRUNC |
2026-10-06T09:00:00 |
| To text | FORMAT_DATETIME |
06 Oct 2026, 09:30 |
| From text | PARSE_DATETIME |
2026-10-06T09:30:00 |
| Date or time part | DATE(), TIME(), EXTRACT |
2026-10-06 |
If you are not sure DATETIME is the right type for your column, read DATE vs DATETIME vs TIMESTAMP first: for anything that records when an event happened, TIMESTAMP is the safer choice. Google’s list is the datetime functions reference.
Note: Originally published in January 2024. Fully updated in October 2026 with every example re-run.
DATETIME: build one
SELECT
DATETIME(2026, 10, 6, 9, 30, 0),
-- 2026-10-06T09:30:00
DATETIME(DATE '2026-10-06'),
-- 2026-10-06T00:00:00
DATETIME(DATE '2026-10-06', TIME '09:30:00'),
-- 2026-10-06T09:30:00
CAST('2026-10-06 09:30:00' AS DATETIME);
-- 2026-10-06T09:30:00A date on its own becomes midnight. A literal can also be written directly: DATETIME '2026-10-06 09:30:00', in the same way as DATE '2026-10-06'.
DATETIME(timestamp, time_zone)
DATETIME(timestamp, time_zone)
This is the form to know. It answers “what did the clock say there at that moment”:
SELECT
DATETIME(TIMESTAMP '2026-10-06 23:30:00+00',
'Asia/Tokyo'),
-- 2026-10-07T08:30:00
DATETIME(TIMESTAMP '2026-10-06 23:30:00+00');
-- 2026-10-06T23:30:00Without the second argument BigQuery uses UTC. Pass the zone every time, or an evening event lands on the wrong local day. The reverse conversion, a local clock time to a moment, is TIMESTAMP(datetime, 'time zone'), covered in timestamp functions.
CURRENT_DATETIME('Europe/Kyiv') returns the current local time for a zone, and UTC without one.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
DATETIME_ADD and DATETIME_SUB
SELECT
DATETIME_ADD(DATETIME '2026-10-06 09:30:00',
INTERVAL 90 MINUTE),
-- 2026-10-06T11:00:00
DATETIME_SUB(DATETIME '2026-10-06 09:30:00',
INTERVAL 1 MONTH);
-- 2026-09-06T09:30:00Parts run from MICROSECOND up to YEAR. Month arithmetic clamps to the last valid day, the same as with dates.
DATETIME_DIFF
SELECT
DATETIME_DIFF(DATETIME '2026-10-06 09:30:00',
DATETIME '2026-10-05 22:00:00',
HOUR),
-- 11
DATETIME_DIFF(DATETIME '2026-10-06 00:10:00',
DATETIME '2026-10-05 23:50:00',
DAY);
-- 1The second result is the one to remember: twenty minutes apart, and the difference in days is 1. DATETIME_DIFF counts part boundaries crossed, and midnight was crossed once. For elapsed time, take the difference in a smaller part, such as MINUTE, and divide.
One more limit, from the type itself: a DATETIME does not know about daylight saving time. Across a clock change, a DATETIME_DIFF in hours is off by one. Durations belong in TIMESTAMP.
DATETIME_TRUNC
SELECT
DATETIME_TRUNC(DATETIME '2026-10-06 09:30:45',
HOUR),
-- 2026-10-06T09:00:00
DATETIME_TRUNC(DATETIME '2026-10-06 09:30:45',
WEEK(MONDAY));
-- 2026-10-05T00:00:00A week starts on Sunday unless you say WEEK(MONDAY) or ISOWEEK. The difference between those two is explained in date functions.
EXTRACT, DATE and TIME
SELECT
EXTRACT(HOUR FROM DATETIME '2026-10-06 09:30:45'),
-- 9
DATE(DATETIME '2026-10-06 09:30:45'),
-- 2026-10-06
TIME(DATETIME '2026-10-06 09:30:45');
-- 09:30:45DATE(datetime) needs no time zone, because a DATETIME has none to convert. LAST_DAY also accepts a datetime and returns a DATE: LAST_DAY(DATETIME '2026-02-10 09:00:00') is 2026-02-28.
FORMAT_DATETIME and PARSE_DATETIME
FORMAT_DATETIME and PARSE_DATETIME
SELECT
FORMAT_DATETIME('%d %b %Y, %H:%M',
DATETIME '2026-10-06 09:30:45'),
-- 06 Oct 2026, 09:30
PARSE_DATETIME('%d/%m/%Y %H:%M',
'06/10/2026 09:30');
-- 2026-10-06T09:30:00Common format elements: %Y year, %m month, %d day, %H hour (24h), %M minute, %S second, %b short month name, %A weekday name.
PARSE_DATETIME stops the query on a string that does not match. On imported data use SAFE.PARSE_DATETIME, which returns NULL instead.
“SAFE with function datetime is not supported”
“SAFE with function datetime is not supported”
SAFE.DATETIME(string_column) returns that error. (With numbers, SAFE.DATETIME(2026, 13, 1, 0, 0, 0) does run and returns NULL.) For a string, use one of these:
SELECT
SAFE_CAST(s AS DATETIME),
SAFE.PARSE_DATETIME('%Y-%m-%d %H:%M:%S', s)
FROM UNNEST(['2026-10-06 25:00:00']) AS s;
-- NULL, NULLSAFE_CAST for the standard YYYY-MM-DD HH:MM:SS layout, SAFE.PARSE_DATETIME for anything else. More in CAST and SAFE_CAST.
Mistakes to avoid
- Treating a
DATETIMEas a moment. It is a clock reading. Two systems in different zones can write the sameDATETIMEfor different moments. - Converting a timestamp without a zone.
DATETIME(ts)is UTC. - Reading
DATETIME_DIFF(..., DAY)as elapsed days. It counts midnights. - Comparing a
DATETIMEto aTIMESTAMP. BigQuery refuses; convert one side with an explicit zone.
Keep the zone decision in one query
Keep the zone decision in one query
Every conversion on this page that takes a time zone is a decision about what “the 6th of October” means for the business. Made in one query, it is a definition. Made separately in each report, it is the reason two reports disagree by a few orders around midnight.
In OWOX Data Marts the query that turns timestamps into local times is a Data Mart: your SQL, with the zone in it, a description and an owner, run in your warehouse. Reports read the local time from there. OWOX does not choose the zone or convert anything by itself. It keeps the conversion you wrote as the only one in use.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
Why does SAFE.DATETIME fail in BigQuery?
With a string argument BigQuery returns 'SAFE with function datetime is not supported'. Use SAFE_CAST(x AS DATETIME) for the standard layout, or SAFE.PARSE_DATETIME with a format string. Both return NULL for an invalid value.
How do I add time to a DATETIME in BigQuery?
DATETIME_ADD(datetime, INTERVAL n part), with parts from MICROSECOND to YEAR. DATETIME_ADD(DATETIME '2026-10-06 09:30:00', INTERVAL 90 MINUTE) returns 2026-10-06T11:00:00. DATETIME_SUB subtracts.
How do I format a DATETIME in BigQuery?
FORMAT_DATETIME(format, datetime). FORMAT_DATETIME('%d %b %Y, %H:%M', DATETIME '2026-10-06 09:30:45') returns '06 Oct 2026, 09:30'. PARSE_DATETIME does the reverse.
How do I convert a TIMESTAMP to a DATETIME in a time zone?
DATETIME(timestamp, 'time zone'). DATETIME(TIMESTAMP '2026-10-06 23:30:00+00', 'Asia/Tokyo') returns 2026-10-07T08:30:00. Without the second argument BigQuery uses UTC.
How do I extract the date or month from a DATETIME?
DATE(datetime) returns the date and TIME(datetime) the time. EXTRACT(MONTH FROM datetime) returns the month number; EXTRACT also takes YEAR, DAY, HOUR, MINUTE, DAYOFWEEK and WEEK.
What is the difference between DATETIME and TIMESTAMP in BigQuery?
A DATETIME is a date and a clock time with no time zone. A TIMESTAMP is one exact moment, stored in UTC. Use TIMESTAMP for when something happened and DATETIME for a local clock reading.
