# 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. Source: https://docs.querylab.io/bigquery/data-types/ Updated: 2026-10-06 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`. | Type | Holds | Notes | | ----------------- | -------------------------------------------------- | -------------------------------------------------------------------- | | `INT64` | Whole numbers, -2^63 to 2^63-1 | The only integer type. | | `FLOAT64` | Double-precision floats | Approximate. Has `NaN` and `Infinity`. | | `NUMERIC` | Exact decimal, 38 digits, 9 after the point | Alias `DECIMAL`. Largest value about 9.9e28. | | `BIGNUMERIC` | Exact decimal, about 76 digits, 38 after the point | Alias `BIGDECIMAL`. | | `BOOL` | `TRUE` or `FALSE` | Alias `BOOLEAN`. | | `STRING` | UTF-8 text | `STRING(L)` caps the length at L characters. | | `BYTES` | Binary data | Shown as base64. | | `DATE` | A calendar day | No time zone. | | `TIME` | A time of day | No date, no time zone. | | `DATETIME` | A date and a time | A wall clock reading, no time zone. | | `TIMESTAMP` | A point in time | Microsecond precision, shown in UTC. | | `INTERVAL` | A duration in months, days and time | Not a number of seconds. | | `RANGE` | A span between two dates, datetimes or timestamps | Start included, end excluded. | | `GEOGRAPHY` | Points, lines and polygons on the Earth | Longitude first. | | `JSON` | A JSON value | See [JSON](https://docs.querylab.io/bigquery/json/). | | `ARRAY`, `STRUCT` | Lists and groups of fields | See [Nested and repeated data](https://docs.querylab.io/bigquery/nested-and-repeated-data/). | How much each type adds to a query's bill is in [How BigQuery bills a query](https://docs.querylab.io/bigquery/how-queries-are-billed/#columns-have-fixed-sizes). ## Integers `INT64` overflows with an error and never wraps around: **the largest INT64 plus one** ```sql SELECT 9223372036854775807 + 1 AS next_id ``` BigQuery rejects this query: ```text Integer Overflow ``` `/` always returns a `FLOAT64`, even between two integers. Use `DIV` for integer division: **7 / 2 is 3.5** ```sql SELECT 7 / 2 AS slash, DIV(7, 2) AS div, MOD(7, 2) AS mod, CAST(7 / 2 AS INT64) AS slash_as_int ``` Result in BigQuery (1 row): | slash | div | mod | slash_as_int | | --- | --- | --- | --- | | 3.5 | 3 | 1 | 4 | `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: **1 / 0** ```sql SELECT 1 / 0 AS ratio ``` BigQuery rejects this query: ```text division by zero: 1 / 0 ``` ## FLOAT64 and NUMERIC `FLOAT64` stores binary fractions, so decimals such as 0.1 are approximations: **0.1 + 0.2 in FLOAT64 and NUMERIC** ```sql 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 ``` Result in BigQuery (1 row): | float_sum | float_equal | numeric_sum | numeric_equal | | --- | --- | --- | --- | | 0.30000000000000004 | false | 0.3 | true | 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: **one third in three types** ```sql SELECT 1 / 3 AS float64_third, NUMERIC '1' / NUMERIC '3' AS numeric_third, BIGNUMERIC '1' / BIGNUMERIC '3' AS bignumeric_third ``` Result in BigQuery (1 row): | float64_third | numeric_third | bignumeric_third | | --- | --- | --- | | 0.3333333333333333 | 0.333333333 | 0.33333333333333333333333333333333333333 | Both types have limits that raise errors. A result above about 9.9e28 overflows `NUMERIC`: **NUMERIC overflow** ```sql SELECT NUMERIC '99999999999999999999999999999.999999999' * 10 AS too_big ``` BigQuery rejects this query: ```text numeric overflow: 99999999999999999999999999999.999999999 * 10 ``` A `NUMERIC` literal with more than 9 decimals is rejected, though casting a float with 10 decimals rounds it to 9: **a NUMERIC literal with ten decimals** ```sql SELECT NUMERIC '1.0000000005' AS ten_decimals ``` BigQuery rejects this query: ```text Invalid NUMERIC literal: "1.0000000005" at [1:8] ``` 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`: **NaN is not NULL and not equal to NaN** ```sql 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 ``` Result in BigQuery (1 row): | nan_equals_nan | nan_is_null | one_over_zero | | --- | --- | --- | | false | false | Infinity | 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 text-number conversion 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: **INT64 = STRING** ```sql SELECT 1 = '1' AS same ``` BigQuery rejects this query: ```text No matching signature for operator = for argument types: INT64, STRING Signature: T1 = T1 Unable to find common supertype for templated argument Input types for : {INT64, STRING} at [1:8] ``` 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: **'1' + 1** ```sql SELECT '1' + 1 AS two ``` BigQuery rejects this query: ```text Could not cast literal "1" to type DATE at [1:8] ``` Write the conversion you mean with `CAST('1' AS INT64) + 1`. In Querylab.io, comparing a numeric column with a string literal, such as `odometer > '1000'`, is a warning in the editor before you run it ([Code quality](https://docs.querylab.io/writing-sql/code-quality/#rules-by-category)). ## CAST and SAFE_CAST `CAST` fails the whole query on the first value it can't convert. `SAFE_CAST` returns `NULL` for that value: **CAST of text that is not a number** ```sql SELECT CAST('abc' AS INT64) AS n ``` BigQuery rejects this query: ```text Bad int64 value: abc ``` **SAFE_CAST and rounding** ```sql 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 ``` Result in BigQuery (1 row): | not_a_number | padded | decimal_text | float_half_up | float_half_down | | --- | --- | --- | --- | --- | | NULL | 42 | NULL | 5 | -5 | `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: **the same 12:00 as an instant and as a wall clock** ```sql 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 ``` Result in BigQuery (1 row): | instant | new_york_clock | clock_as_utc | clock_as_new_york | | --- | --- | --- | --- | | 2026-03-08 12:00:00 UTC | 2026-03-08 08:00:00 | 2026-03-08 12:00:00 UTC | 2026-03-08 16:00:00 UTC | 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: **nine fractional digits** ```sql SELECT TIMESTAMP '2026-03-08 12:00:00.123456789+00' AS ts ``` BigQuery rejects this query: ```text Invalid TIMESTAMP literal at [1:8] ``` ### 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: **DATE + INTERVAL vs DATE_ADD** ```sql SELECT DATE '2026-01-31' + INTERVAL 1 MONTH AS plus_operator, DATE_ADD(DATE '2026-01-31', INTERVAL 1 MONTH) AS date_add ``` Result in BigQuery (1 row): | plus_operator | date_add | | --- | --- | | 2026-02-28 00:00:00 | 2026-02-28 | 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: **TIMESTAMP + INTERVAL 1 MONTH** ```sql SELECT TIMESTAMP '2026-01-31 10:00:00+00' + INTERVAL 1 MONTH AS next_month ``` BigQuery rejects this query: ```text TIMESTAMP +/- INTERVAL is not supported for intervals with non-zero MONTH or YEAR part. ``` Convert the timestamp to a date or datetime first, or use `TIMESTAMP_ADD` with days or hours. ## RANGE A `RANGE` is a span with an included start and an excluded end, so two consecutive months share no day: **the end of a range is not in it** ```sql 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 ``` Result in BigQuery (1 row): | range_start | range_end | has_start | has_end | | --- | --- | --- | --- | | 2026-01-01 | 2026-02-01 | true | false | 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](https://docs.querylab.io/bigquery/how-queries-are-billed/) uses: **characters and bytes** ```sql SELECT LENGTH('héllo') AS characters, BYTE_LENGTH('héllo') AS bytes ``` Result in BigQuery (1 row): | characters | bytes | | --- | --- | | 5 | 6 | Comparisons are case-sensitive and accent-sensitive unless you set a collation. See [Strings, regular expressions and collation](https://docs.querylab.io/bigquery/strings-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: **longitude, then latitude** ```sql SELECT ST_GEOGPOINT(13.4, 52.5) AS berlin ``` Result in BigQuery (1 row): | berlin | | --- | | POINT(13.4 52.5) | More in [Geography](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/bigquery/safe-functions/). Compare 0.1 + 0.2 in FLOAT64 and in NUMERIC. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Data types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types) - [Conversion rules](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules) - [Conversion functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions)