---
title: "CAST"
description: "CAST converts a value from one data type to another, such as turning a string into an integer or a timestamp into a date."
canonical: "https://motherduck.com/glossary/cast/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Data Transformation | MotherDuck Docs"
    url: "https://motherduck.com/docs/integrations/transformation/"
---

# CAST

> CAST converts a value from one data type to another, such as turning a string into an integer or a timestamp into a date.

## Overview
`CAST(expression AS type)` explicitly converts a value to a target data type. It's the standard ANSI SQL way to control type conversions rather than relying on implicit coercion, which varies between database engines and can silently produce unexpected results.

```sql
SELECT CAST('42' AS INTEGER) AS as_int,
       CAST(order_date AS DATE) AS just_the_date,
       CAST(price AS VARCHAR) AS price_text;
```

<glossary-callout guide="duckdb-cheatsheet-full" />

## DuckDB's :: shorthand
DuckDB (like PostgreSQL) supports a compact `::` cast operator that's equivalent to `CAST`:

```sql
SELECT '42'::INTEGER AS as_int,
       amount::DECIMAL(10,2) AS amount_2dp;
```

Both forms produce identical results; `::` is simply less verbose and popular in ad hoc queries, while `CAST(... AS ...)` is more portable across databases and often preferred in production SQL.

## Failed casts
A `CAST` that cannot succeed -- for example, `CAST('not a number' AS INTEGER)` -- raises an error and aborts the query. When you want invalid conversions to produce `NULL` instead of failing, use `TRY_CAST` (DuckDB and several other engines) rather than `CAST`.

## Casting complex types
Because DuckDB has rich nested types, `CAST` also works on lists, structs, and other composite types:

```sql
SELECT CAST([1, 2, 3] AS DOUBLE[]) AS as_doubles;
```

## When implicit casting kicks in
DuckDB will sometimes cast automatically -- for example, comparing an `INTEGER` column to a string literal, or inserting a `VARCHAR` value into a `DATE` column -- following its type coercion rules. Explicit `CAST` is preferred whenever the conversion isn't obvious, since it documents intent and avoids relying on engine-specific coercion behavior.