BigQuery data types

BigQuery has one integer type (INT64), exact decimals (NUMERIC, BIGNUMERIC) and an approximate FLOAT64. What each type holds, where BigQuery differs from Postgres or MySQL, and which conversions fail.

BigQuery has fewer types than most databases. There is one integer type, INT64, one approximate number, FLOAT64, and two exact decimals, NUMERIC and BIGNUMERIC. There is no MONEY type, and no separate SMALLINT storage. The names you know (INT, BIGINT, SMALLINT, TINYINT, BYTEINT, INTEGER) are accepted and all mean INT64.

How much each type adds to a query’s bill is in How BigQuery bills a query.

Integers

INT64 overflows with an error and never wraps around:

SELECT 9223372036854775807 + 1 AS next_id

/ always returns a FLOAT64, even between two integers. Use DIV for integer division:

SELECT
  7 / 2 AS slash,
  DIV(7, 2) AS div,
  MOD(7, 2) AS mod,
  CAST(7 / 2 AS INT64) AS slash_as_int

CAST(7 / 2 AS INT64) gives 4, not 3, because the cast rounds. Casting a FLOAT64 to INT64 rounds halves away from zero, so 4.5 becomes 5 and -4.5 becomes -5. If you wanted the whole part, use DIV or TRUNC.

Dividing by zero is an error too. SAFE_DIVIDE(1, 0) returns NULL instead:

SELECT 1 / 0 AS ratio

FLOAT64 and NUMERIC

FLOAT64 stores binary fractions, so decimals such as 0.1 are approximations:

SELECT
  0.1 + 0.2 AS float_sum,
  0.1 + 0.2 = 0.3 AS float_equal,
  NUMERIC '0.1' + NUMERIC '0.2' AS numeric_sum,
  NUMERIC '0.1' + NUMERIC '0.2' = NUMERIC '0.3' AS numeric_equal

The float sum is 0.30000000000000004 and is not equal to 0.3. NUMERIC stores decimal digits and gets it right. Use NUMERIC for money and anything else that is compared for equality. Google’s documentation also notes that SUM over floats can depend on the order in which rows are added, so the same query can return slightly different totals on two runs.

A decimal literal like 0.1 is a FLOAT64. When you write a decimal where a NUMERIC is expected, BigQuery converts the literal, and the result is the nearest NUMERIC. To avoid the float step, write NUMERIC '0.1' or compute with NUMERIC columns.

NUMERIC keeps 9 digits after the decimal point and BIGNUMERIC keeps 38:

SELECT
  1 / 3 AS float64_third,
  NUMERIC '1' / NUMERIC '3' AS numeric_third,
  BIGNUMERIC '1' / BIGNUMERIC '3' AS bignumeric_third

Both types have limits that raise errors. A result above about 9.9e28 overflows NUMERIC:

SELECT NUMERIC '99999999999999999999999999999.999999999' * 10 AS too_big

A NUMERIC literal with more than 9 decimals is rejected, though casting a float with 10 decimals rounds it to 9:

SELECT NUMERIC '1.0000000005' AS ten_decimals

You can restrict a column with NUMERIC(P, S), where P is the total number of digits and S the number after the point. A value with more decimals than S is rounded, and a value with more than P digits is an error. For example NUMERIC(5, 2) holds up to 999.99.

NaN and infinity

FLOAT64 can hold NaN and Infinity. NaN is not equal to itself and is not NULL:

SELECT
  CAST('NaN' AS FLOAT64) = CAST('NaN' AS FLOAT64) AS nan_equals_nan,
  CAST('NaN' AS FLOAT64) IS NULL AS nan_is_null,
  IEEE_DIVIDE(1, 0) AS one_over_zero

Arithmetic that overflows a finite input is an error (1e308 * 10 fails with “double overflow”). To get an infinity from a division, ask for it with IEEE_DIVIDE.

No implicit conversion between text and numbers

BigQuery converts a type to another automatically only in a few cases. Integers widen to NUMERIC, BIGNUMERIC and FLOAT64, and a DATE widens to DATETIME. String literals can become DATE, DATETIME, TIME and TIMESTAMP, which is why WHERE order_date >= '2026-01-01' works. Nothing converts a string to a number or the other way round on its own:

SELECT 1 = '1' AS same

Sometimes the message points at the wrong thing. This one is about a string and a number, but BigQuery tried to read the string as a date, because a string literal is allowed to become one:

SELECT '1' + 1 AS two

Write the conversion you mean with CAST('1' AS INT64) + 1.

CAST and SAFE_CAST

CAST fails the whole query on the first value it can’t convert. SAFE_CAST returns NULL for that value:

SELECT CAST('abc' AS INT64) AS n

SELECT
  SAFE_CAST('abc' AS INT64) AS not_a_number,
  SAFE_CAST(' 42 ' AS INT64) AS padded,
  SAFE_CAST('4.5' AS INT64) AS decimal_text,
  CAST(4.5 AS INT64) AS float_half_up,
  CAST(-4.5 AS INT64) AS float_half_down

SAFE_CAST accepts the padded ' 42 ' and rejects '4.5' for an integer. Since a NULL can hide bad data, use SAFE_CAST where you will count or inspect the failures, and CAST where a bad value should stop the query.

Dates, times and time zones

DATE, TIME and DATETIME have no time zone. TIMESTAMP is a point in time, stored as UTC with microsecond precision. A timestamp literal without a zone is read as UTC. Converting between the two needs a zone, and the default is UTC:

SELECT
  TIMESTAMP '2026-03-08 12:00:00+00' AS instant,
  DATETIME(TIMESTAMP '2026-03-08 12:00:00+00', 'America/New_York') AS new_york_clock,
  CAST(DATETIME '2026-03-08 12:00:00' AS TIMESTAMP) AS clock_as_utc,
  TIMESTAMP(DATETIME '2026-03-08 12:00:00', 'America/New_York') AS clock_as_new_york

The first column is noon UTC. The second shows what a New York clock reads at that moment, 08:00. Turning the noon wall clock into a timestamp gives noon UTC or, if you say so, noon in New York, which is 16:00 UTC. Mixing these up moves events by hours. Store events as TIMESTAMP, and keep a time zone name next to a DATETIME if you need to convert it later.

Precision is microseconds. A literal with nanoseconds is an error:

SELECT TIMESTAMP '2026-03-08 12:00:00.123456789+00' AS ts

Intervals and months

An INTERVAL keeps months, days and time as separate parts, because a month has no fixed length. Adding a month to the 31st clips to the end of the month:

SELECT
  DATE '2026-01-31' + INTERVAL 1 MONTH AS plus_operator,
  DATE_ADD(DATE '2026-01-31', INTERVAL 1 MONTH) AS date_add

The + operator returns a DATETIME here, not a DATE. DATE_ADD returns a DATE. When a column ends up as a datetime that you didn’t expect, this is a common cause. Adding a month to a TIMESTAMP is not allowed at all:

SELECT TIMESTAMP '2026-01-31 10:00:00+00' + INTERVAL 1 MONTH AS next_month

Convert the timestamp to a date or datetime first, or use TIMESTAMP_ADD with days or hours.

RANGE

A RANGE<DATE> is a span with an included start and an excluded end, so two consecutive months share no day:

WITH january AS (
  SELECT RANGE(DATE '2026-01-01', DATE '2026-02-01') AS r
)
SELECT
  RANGE_START(r) AS range_start,
  RANGE_END(r) AS range_end,
  RANGE_CONTAINS(r, DATE '2026-01-01') AS has_start,
  RANGE_CONTAINS(r, DATE '2026-02-01') AS has_end
FROM january

The query reads the start and end separately. The console prints the whole range as [2026-01-01, 2026-02-01). Ranges of DATE, DATETIME and TIMESTAMP are supported.

Strings and bytes

A STRING is UTF-8 text. LENGTH counts characters and BYTE_LENGTH counts bytes, and the byte count is what query billing uses:

SELECT
  LENGTH('héllo') AS characters,
  BYTE_LENGTH('héllo') AS bytes

Comparisons are case-sensitive and accent-sensitive unless you set a collation. See Strings, regular expressions and collation.

GEOGRAPHY

Geography functions take longitude first, then latitude. ST_GEOGPOINT(13.4, 52.5) is Berlin, and swapping the arguments puts the point in the wrong place without an error:

SELECT ST_GEOGPOINT(13.4, 52.5) AS berlin

More in Geography.

Division by zero, overflow and a bad cast stop the query in BigQuery. The SAFE. prefix, SAFE_CAST and SAFE_DIVIDE turn them into NULL on request. See SAFE functions.