# SQL CAST: Syntax, Examples, and Data Type Conversion

> Learn how SQL CAST works, explore its syntax and examples, convert data types, compare CAST with CONVERT and TRY_CAST, and troubleshoot common errors.

- Canonical URL: https://www.puppygraph.com/blog/sql-cast
- Published: 2026-11-20
- Author: Hao Wu

A weight stored as text needs numeric conversion before it can participate reliably in arithmetic. A parcel identifier such as `00127` needs its leading zeros preserved. Using SQL CAST correctly starts with deciding what a value means, then choosing a type that preserves that meaning.

This guide covers CAST syntax, practical conversions, error handling, and differences between database systems. The main examples use PostgreSQL. Examples for SQL Server and other databases are labeled because shared syntax does not guarantee identical conversion behavior.

## What is CAST in SQL?

CAST explicitly converts an expression to a specified data type. The expression can be a literal, a column reference, or a calculation. The familiar `CAST(expression AS type)` form is [SQL-standard syntax](https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS).

```sql
SELECT CAST('12.50' AS DECIMAL(8, 2)) AS weight_kg;
```

In PostgreSQL, this returns the numeric value `12.50`. The quotes identify the input as a string literal; `DECIMAL(8, 2)` specifies the result type.

A CAST in a SELECT changes the value produced by that expression. It does not rewrite the source column or change its declared type. Persisting converted values requires a separate operation, such as inserting them into a typed destination table.

Databases also apply implicit conversions where their rules allow. An explicit CAST makes the intended target visible in the query; Microsoft's [conversion overview](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine) explains that distinction for SQL Server.

## SQL CAST syntax and parameters

The general form is:

```
CAST(expression AS target_data_type)
```

**Expression.** Supply the value to convert. For example, `weight_kg_text` reads a column, while `'12.50'` supplies a literal. An alias follows the closing parenthesis: `CAST(weight_kg_text AS DECIMAL(8, 2)) AS weight_kg`.

**Target data type.** Choose a type supported by the database and by the particular conversion. Common examples include `INTEGER`, `DECIMAL`, `VARCHAR`, and `DATE`, but names and permitted conversions vary by dialect.

**Length, precision, and scale.** These belong to the target type. In `VARCHAR(12)`, the length bounds the string. In `DECIMAL(8, 2)`, precision allows eight total digits and scale reserves two fractional digits, leaving six for the integer part. PostgreSQL documents these rules under [numeric types](https://www.postgresql.org/docs/current/datatype-numeric.html).

Write the dimensions deliberately. A type chosen only to fit today's example may truncate a longer identifier or overflow when tomorrow's measurement arrives.

## How does the CAST function work in SQL?

The database resolves the source expression and target type, checks whether a conversion exists, and applies that conversion's rules. Converting text to a number requires interpreting its characters. Converting one numeric type to another may change its range or precision. A conversion can succeed while discarding information.

The position of the cast also matters. PostgreSQL's [integer division](https://www.postgresql.org/docs/current/functions-math.html) discards the fractional part. Compare these expressions:

```sql
SELECT
    CAST(7 / 2 AS DECIMAL(8, 2)) AS cast_after_division,
    CAST(7 AS DECIMAL(8, 2)) / 2 AS cast_before_division;
```

The first result is `3.00`: integer division already produced `3` before the cast. The second is numerically `3.5`, because converting an operand changes the arithmetic. Casting the final result cannot recover information lost earlier.

![How does the CAST function work in SQL?](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/2aa2aff7-ece5-451c-a827-92e00bb5f620/hao-wu-20261120-9bb7c5f0481334c73a4835ab.png)

Figure: In PostgreSQL, CAST in a SELECT converts the query's value without rewriting the source column. Malformed numeric text raises an error.

## SQL CAST examples

Consider a parcel feed that arrives with weights and shipment dates stored as text. Create this small PostgreSQL fixture:

```sql
CREATE TEMP TABLE raw_parcels (
    parcel_id VARCHAR(12),
    weight_kg_text VARCHAR(30),
    shipped_on_text VARCHAR(10)
);

INSERT INTO raw_parcels VALUES
    ('00127', '12.50',  '2026-10-01'),
    ('00128', '7.25',   '2026-10-01'),
    ('00129', '100.00', '2026-10-02');
```

Convert the fields according to their meaning:

```sql
SELECT
    parcel_id,
    CAST(weight_kg_text AS DECIMAL(8, 2)) AS weight_kg,
    CAST(shipped_on_text AS DATE) AS shipped_on
FROM raw_parcels
ORDER BY parcel_id;
```

| **parcel\_id** | **weight\_kg** | **shipped\_on** |
| --- | --- | --- |
| 00127 | 12.50 | 2026-10-01 |
| 00128 | 7.25 | 2026-10-01 |
| 00129 | 100.00 | 2026-10-02 |

The result contains a decimal weight and a date. The parcel identifier remains a string. It consists of digits, but its job is to identify a parcel, not to participate in arithmetic.

## How to convert data types using CAST in SQL

Choose the target from the operation you need to perform. To find parcels heavier than ten kilograms, compare converted weights with a numeric threshold:

```sql
SELECT
    parcel_id,
    CAST(weight_kg_text AS DECIMAL(8, 2)) AS weight_kg
FROM raw_parcels
WHERE CAST(weight_kg_text AS DECIMAL(8, 2)) > 10
ORDER BY weight_kg;
```

This returns `00127` at `12.50` kg and `00129` at `100.00` kg. Comparing the original strings would use text-comparison rules, which do not express the intended weight comparison.

The converted values can also feed an aggregate:

```sql
SELECT SUM(CAST(weight_kg_text AS DECIMAL(8, 2))) AS total_weight_kg
FROM raw_parcels;
```

The total is `119.75`. Both queries leave the imported text intact.

For recurring analysis, validate the feed and store accepted weights in a numeric column. Keep the raw input where it is needed for diagnosis, and handle rejected values explicitly. Repeating a CAST in every report spreads the same conversion policy across many queries.

## SQL CAST for strings, numbers, and dates

**Strings and identifiers.** Casting `'00127'` to an integer produces `127`. Converting it back to text cannot recover the original width. Keep identifiers as strings when leading zeros carry meaning.

A string target can also discard characters. In PostgreSQL, an explicit cast to a bounded character type [truncates an overlong value](https://www.postgresql.org/docs/current/datatype-character.html):

```sql
SELECT CAST('00127' AS VARCHAR(3)) AS shortened_id;
```

The result is `001`. Successful execution therefore does not establish that the identifier survived intact.

**Numbers.** Use a decimal type when the application needs a defined decimal scale. In PostgreSQL:

```sql
SELECT CAST(12.345 AS DECIMAL(8, 2)) AS rounded_weight;
```

The result is `12.35`. PostgreSQL rounds to the requested scale, then checks whether the integer portion fits. A value of `1000000.00` cannot fit in `DECIMAL(8, 2)` and raises an error. These behaviors follow its [precision and scale rules](https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL).

A cast also preserves the unit unless you change the value separately. Converting `'12.50'` to a decimal does not convert kilograms to pounds or validate that the source used kilograms in the first place.

**Dates and timestamps.** PostgreSQL accepts an ISO-formatted date string:

```sql
SELECT CAST('2026-10-01' AS DATE) AS shipped_on;
```

This produces a date value. PostgreSQL's [date input and output settings](https://www.postgresql.org/docs/current/datatype-datetime.html) affect interpretation and presentation; a string such as `01/02/2026` is ambiguous without an agreed format.

Casting a timestamp to a date drops the time component. For PostgreSQL's `timestamp with time zone` (`timestamptz`), the cast uses the session's [TimeZone setting](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TIMEZONE) to determine the calendar day. Set the intended zone before casting, so the result reflects either the UTC day or the depot's local day.

## CAST vs CONVERT in SQL

In SQL Server, CAST and CONVERT both perform type conversion. CONVERT adds an optional style argument for supported conversions, including date parsing and formatting. Microsoft's [CAST and CONVERT reference](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) defines the syntax and styles.

| **SQL Server comparison** | **CAST** | **CONVERT** |
| --- | --- | --- |
| Call form | `CAST(value AS type)` | `CONVERT(type, value [, style])` |
| Explicit format style | No style argument | Style argument for supported conversions |
| Invalid numeric text, such as `'heavy'` to `INT` | Raises an error | Raises an error |
| Typical choice | Straightforward type conversion | Conversion requiring a documented style |

For example, SQL Server style `112` represents `yyyyMMdd`:

```sql
-- SQL Server
SELECT
    CONVERT(DATE, '20261001', 112) AS shipped_on,
    CONVERT(CHAR(8), CAST('2026-10-01' AS DATE), 112) AS date_text;
```

The first expression produces a date; the second produces the string `20261001`. Choose CONVERT when the style is part of the requirement. Its extra parameter does not make it a universal replacement for CAST.

The name is dialect-specific. [MySQL's CONVERT](https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html) uses `CONVERT(expression, type)` for conversion and `CONVERT(expression USING character_set)` for character-set conversion. SQL Server's argument order and date-style codes do not transfer to MySQL.

## CAST vs TRY\_CAST in SQL

In SQL Server, [TRY\_CAST](https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql) returns NULL when a supported conversion fails for the supplied value. It is useful when malformed input should be identified without stopping the query.

```sql
-- SQL Server
SELECT
    TRY_CAST('12.50' AS DECIMAL(8, 2)) AS valid_weight,
    TRY_CAST('heavy' AS DECIMAL(8, 2)) AS invalid_weight;
```

The results are `12.50` and NULL.

| **SQL Server behavior** | **CAST** | **TRY\_CAST** |
| --- | --- | --- |
| Valid value and permitted conversion | Returns converted value | Returns converted value |
| Malformed numeric text | Raises an error | Returns NULL |
| NULL input for a permitted conversion | Returns NULL | Returns NULL |
| Explicitly prohibited type conversion | Raises an error | Still raises an error |

TRY\_CAST handles conversion failures, not every possible query error. It also does not flag information loss that the conversion permits. A successful rounded result remains a successful conversion.

Use CAST when a conversion error should stop processing. Use TRY\_CAST when the query will deliberately retain, classify, or exclude failed values. If SQL Server date conversion also needs a format style, [TRY\_CONVERT](https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql) provides the corresponding style argument.

## How to handle NULL values and conversion errors

A missing value and an invalid value need different treatment. In PostgreSQL, `CAST(NULL AS DECIMAL(8, 2))` produces a typed NULL. An empty string is still a string and cannot be cast directly to that decimal type.

When the feed defines blanks as missing, normalize them explicitly:

```sql
SELECT CAST(NULLIF(TRIM('   '), '') AS DECIMAL(8, 2)) AS weight_kg;
```

The result is NULL. TRIM removes the spaces, and [NULLIF](https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-NULLIF) turns the empty string into NULL. This handles the stated blank-input policy; it does not make `'heavy'` a valid number.

COALESCE supplies a fallback for NULL. It does not catch conversion exceptions. In PostgreSQL, `COALESCE(CAST('heavy' AS DECIMAL(8, 2)), 0)` fails while evaluating the cast, before a fallback can help. The [COALESCE documentation](https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE) describes choosing the first non-NULL argument, not exception handling.

For dirty imports, preserve both the source and its status. This standalone example separates missing, blank, and invalid weights using [TRIM](https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql), available in SQL Server 2017 and later:

```sql
-- SQL Server
WITH raw_parcels AS (
    SELECT *
    FROM (VALUES
        ('00127', '12.50'),
        ('00128', NULL),
        ('00129', '   '),
        ('00130', 'heavy'),
        ('00131', '1000000.00')
    ) AS v(parcel_id, weight_kg_text)
), normalized AS (
    SELECT *, NULLIF(TRIM(weight_kg_text), '') AS normalized_weight
    FROM raw_parcels
), converted AS (
    SELECT *, TRY_CAST(normalized_weight AS DECIMAL(8, 2)) AS weight_kg
    FROM normalized
)
SELECT
    parcel_id,
    weight_kg_text,
    weight_kg,
    CASE
        WHEN weight_kg_text IS NULL THEN 'missing'
        WHEN normalized_weight IS NULL THEN 'blank'
        WHEN weight_kg IS NULL THEN 'invalid'
        ELSE 'ok'
    END AS conversion_status
FROM converted
ORDER BY parcel_id;
```

| **parcel\_id** | **weight\_kg\_text** | **weight\_kg** | **conversion\_status** |
| --- | --- | --- | --- |
| 00127 | `'12.50'` | 12.50 | ok  |
| 00128 | NULL | NULL | missing |
| 00129 | `'   '` | NULL | blank |
| 00130 | `'heavy'` | NULL | invalid |
| 00131 | `'1000000.00'` | NULL | invalid |

Quoted entries show the raw text; the blank input contains three spaces.

The final row contains numeric text but exceeds the target range. Keeping it distinct from missing input makes the rejection actionable. Replacing every failed conversion with zero would turn unknown weights into apparently measured zero weights and distort downstream analysis.

## SQL CAST across different database systems

The CAST shape is widely shared. Target types, accepted inputs, and failure behavior still need a dialect check.

| **Database** | **Example** | **Conversion behavior to account for** |
| --- | --- | --- |
| PostgreSQL | `CAST('42' AS INTEGER)` | Also supports `expression::type`; a conversion must exist and the value must satisfy its input rules. |
| SQL Server | `CAST('42' AS INT)` | TRY\_CAST supports NULL-on-failure handling for permitted conversions; CONVERT offers format styles. |
| MySQL | `CAST('42' AS SIGNED)` | Uses its own set of cast targets, including SIGNED and UNSIGNED; invalid inputs may produce warnings and coerced values. |
| BigQuery | `CAST('42' AS INT64)` | SAFE\_CAST returns NULL for runtime conversion errors; impossible type conversions still fail during analysis. |
| Snowflake | `CAST('42' AS NUMBER)` | TRY\_CAST accepts string expressions and supports a subset of target types. |
| SQLite | `CAST('42' AS INTEGER)` | Casting text to INTEGER reads a numeric prefix; text with no integer prefix becomes zero. |

These distinctions are documented in the [PostgreSQL cast syntax](https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS), [MySQL cast functions](https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html), [BigQuery conversion functions](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions#safe_cast), and [Snowflake TRY\_CAST reference](https://docs.snowflake.com/en/sql-reference/functions/try_cast). MySQL's [SQL mode documentation](https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sql-mode-strict) also distinguishes warnings in SELECT statements from stricter handling of data-changing statements.

SQLite makes the portability issue concrete:

```sql
-- SQLite
SELECT CAST('heavy' AS INTEGER) AS converted_value;
```

This returns `0`, following SQLite's [CAST expression rules](https://sqlite.org/lang_expr.html#castexpr). The equivalent PostgreSQL conversion raises an error. Testing only valid inputs will miss this difference; include malformed text, blanks, NULLs, and boundary values when porting conversion logic.

## Common SQL CAST errors and how to fix them

**Invalid input syntax.** Inspect the actual source string. A weight such as `'12.50 kg'` contains a unit suffix, while `'1,250'` requires an agreed separator convention. Parse the feed's documented format and preserve rejects. Removing punctuation indiscriminately can change the intended number.

**Overflow or lost precision.** Check both the target's range and the precision the application requires. Increasing decimal scale leaves fewer integer digits if total precision stays fixed. Test values near the boundary, including values that round across it. For identifiers, check string length and preserve leading zeros.

**Ambiguous dates.** Establish the source format before converting. Use the database's format-aware parsing facilities when necessary, such as SQL Server CONVERT with an explicit style. A conversion that chooses the wrong month and day can succeed without producing an error.

**Casting too late.** Convert operands before arithmetic that would otherwise lose information. The earlier `7 / 2` example shows why changing the result type afterward cannot repair the calculation.

**Unexpected query plans.** Casting a column inside a filter changes the expression being searched. An ordinary index on the original column may not support that predicate as intended. Check the execution plan; consider correctly typed stored columns or an appropriate expression index. PostgreSQL's [expression indexes](https://www.postgresql.org/docs/current/indexes-expressional.html) support indexing derived expressions, with additional maintenance work on writes.

Correct types also matter when records become part of a relationship model. In the parcel example, retaining `00127` preserves the identifier used to link its records. If separate tables record parcels inside bags, cages, and outbound containers, following those containment paths is a further analysis over the same data.

[PuppyGraph](https://www.puppygraph.com/) is the first and only zero-ETL graph query engine that queries existing tables as a graph, allowing recorded relationships such as nested parcel containment to be explored as paths. Its distributed engine queries tables in place in SQL databases, data warehouses, and data lakes or lakehouses, including direct reads of Iceberg and Delta Lake. Connect the sources, define a [graph schema mapping tables to nodes and edges](https://docs.puppygraph.com/modeling/), and query with openCypher and Gremlin, without ETL into a separate graph database; the same tables remain queryable with SQL.

![Benefits of Elasticsearch Graph](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/d0e40fcf-cf5a-40b7-97c1-5f5be25de9b6/hao-wu-20261120-4b2587a755bbd5c9068284ee.png)

Figure: PuppyGraph queries the existing tables in place, so graph and SQL queries share one copy of the data and no pipeline keeps a graph copy in sync.

![Benefits of Elasticsearch Graph](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/37a756bf-deab-4cae-b1f3-aecb24bff3b1/hao-wu-20261120-5820007cae8f617f5bb57a14.png)

Figure: Companies that use PuppyGraph.

![Benefits of Elasticsearch Graph](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/5f9c5853-ffc3-40c1-9bfe-b097ccf4296c/hao-wu-20261120-4492653ad4ba877e25923f1c.png)

Figure: Engineering teams at PuppyGraph customers on what they use it for.

## Conclusion

SQL CAST makes a conversion explicit, but the target type and database rules determine what survives. Choose types according to meaning, convert before arithmetic loses information, and keep missing values distinguishable from rejected inputs. Test loss of precision and malformed values as carefully as successful conversions.

Try the forever-free [PuppyGraph Developer Edition](https://www.puppygraph.com/download-confirmation) and [book a demo](https://www.puppygraph.com/book-demo) with the team to see how openCypher and Gremlin queries follow relationships across warehouse and lakehouse tables, with no graph-specific ETL, using the identifiers and properties your SQL workflows maintain.
