# SQL CAST as Date: Convert Strings and Datetimes to Dates

If you need to compare, sort, or group by calendar day, you often have to convert text or a timestamp into a true date. The standard way is `CAST(expression AS DATE)`, which returns only the year, month, and day. This article shows how SQL CAST as Date works across SQL Server, MySQL, PostgreSQL, and SQLite, plus how to handle strings that do not match the expected format.

## Quick Answer

- `CAST(value AS DATE)` converts a string or datetime to a date, dropping any time portion.
- SQL Server also offers `CONVERT(DATE, value, style)`, where the style number controls how ambiguous strings are parsed [1].
- MySQL and PostgreSQL accept `CAST('2024-03-01' AS DATE)` with ISO-style strings, and PostgreSQL also supports the `::date` shorthand [2].
- SQLite has no dedicated DATE storage class, so `CAST(x AS DATE)` returns the numeric year, not a formatted date.
- To go the other direction, use `CAST(date_value AS VARCHAR)` or `CONVERT(VARCHAR, date_value, style)` to format a date as text [1].

## Before You Start

You need to know three things before you write the cast.

First, the target type. `DATE` stores a calendar day with no time. `DATETIME`, `DATETIME2`, and `TIMESTAMP` store a day plus a clock time. Casting a datetime to a date drops the time portion entirely [1].

Second, the source type. Casting a `DATE` to a `DATETIME` is usually safe, but the reverse can fail when the year is out of range. In SQL Server, `datetime` starts at 1753, while `date` and `datetime2` start at 0001, so a date like `1500-01-01` cannot be cast to `datetime` and raises an out-of-range error [1].

Third, the string format. A string like `'2024-03-01'` is unambiguous. A string like `'03/04/2024'` is not, because it could mean March 4 or April 3 depending on the server's language and date format settings. When the format is ambiguous, use an explicit style code with `CONVERT` [1].

| Database | Cast syntax | Notes |
|---|---|---|
| SQL Server | `CAST(x AS DATE)` or `CONVERT(DATE, x, style)` | Style code controls string parsing [1] |
| MySQL | `CAST(x AS DATE)` | Temporal conversions may alter or lose information [3] |
| PostgreSQL | `CAST(x AS DATE)` or `x::date` | Explicit cast syntax is standard [2] |
| SQLite | `CAST(x AS DATE)` | Returns a numeric affinity, not a formatted date |

## Step by Step

1. Identify the column and its current type. Run a quick `SELECT` on a few rows to see whether the value looks like `'2024-03-01'`, `'2024-03-01 14:30:00'`, or something else.

2. Choose the target type. Use `DATE` when you only care about the calendar day. Use `DATETIME` or `TIMESTAMP` when the clock time matters.

3. Write the cast. The general form is:

```sql
CAST(expression AS DATE)
```

4. For SQL Server strings with ambiguous formats, switch to `CONVERT` and supply a style number:

```sql
CONVERT(DATE, '03/04/2024', 101) -- 101 means mm/dd/yyyy
```

5. Test on a small sample before running the cast across a large table. A single bad row can abort the whole query.

6. Use the cast in `WHERE`, `GROUP BY`, or `ORDER BY` when you want date-level comparisons instead of timestamp-level ones.

## Worked Example

The `events` table below stores event dates as text. The goal is to list events that happen after March 1, 2024, using date semantics instead of string comparison.

| event_id | event_name | event_date |
|---|---|---|
| 1 | Kickoff | 2024-02-28 |
| 2 | Design Review | 2024-03-01 |
| 3 | Sprint Planning | 2024-03-05 |
| 4 | Demo Day | 2024-03-15 |
| 5 | Retrospective | 2024-03-22 |

```sql
SELECT event_id, event_name, CAST(event_date AS DATE) AS event_date FROM events WHERE CAST(event_date AS DATE) > '2024-03-01' ORDER BY event_date;
```

The query casts the text `event_date` to `DATE` in both the select list and the filter, so the comparison uses date logic and the output is ordered by date.

| event_id | event_name | event_date |
|---|---|---|

The result is empty. That happens because SQLite has no native `DATE` type, so `CAST(event_date AS DATE)` returns the numeric year value, and comparing that number to the string `'2024-03-01'` matches nothing. The result was checked with an equivalent SQLite query. In a database with a real `DATE` type, the same logic returns the three rows after March 1 (Sprint Planning, Demo Day and Retrospective).

This is a useful lesson. The cast syntax is portable, but the behavior depends on whether the engine actually has a date type. If you want to see how the reverse conversion works, read [SQL CAST as String: Convert Data Types with Examples](/blog/data-analysis/sql-cast-as-string-convert-types).

## Other Ways to Do It

**CONVERT with a style code (SQL Server).** `CONVERT` accepts a third argument that tells the engine how to read a string. Style `101` is `mm/dd/yyyy`, style `112` is `yyyymmdd`, and style `126` is ISO 8601 [1]. This is the safest option for non-ISO input.

**The `::` shorthand (PostgreSQL).** PostgreSQL lets you write `event_date::date` instead of `CAST(event_date AS DATE)`. Both are explicit casts and behave the same way [2].

**Date functions.** Instead of casting, you can extract the day with `DATE()` in MySQL or `date_trunc('day', ts)` in PostgreSQL to strip the time portion [4]. These return a date-like value without a formal cast.

**Formatting back to text.** To turn a date into a string, use `CAST(date_value AS VARCHAR)` or `CONVERT(VARCHAR(10), date_value, 23)`, where style 23 gives `yyyy-mm-dd` [1]. See [SQL Convert Date: Functions, Formats and Examples](/blog/data-analysis/sql-convert-date) for the full style list.

**Filtering to today.** If your goal is a "today" filter, casting is often unnecessary. Use [SQL WHERE Date Is Today: Syntax and Examples](/blog/data-analysis/sql-where-date-is-today) or [SQL CURRENT_DATE: Syntax, Examples and Today's Date Queries](/blog/data-analysis/sql-current-date-function) instead.

## Troubleshooting

**Conversion failed when converting date and time from character string.** The string does not match any format the engine recognizes. Check for stray spaces, mixed separators, or a day-month order the server does not expect. In SQL Server, add a style code to `CONVERT` [1].

**The conversion of a date data type to a datetime data type resulted in an out-of-range value.** You are casting a date before 1753 into a SQL Server `datetime` column. Cast to `datetime2` instead, which supports years from 0001 [1].

**The query returns nothing.** This is the SQLite behavior shown above. Confirm your engine has a real date type, or compare using string formats that sort correctly, such as `yyyy-mm-dd`.

**Dates shift by one day.** This usually means a time zone conversion happened somewhere, or the string was parsed as `dd/mm` when you meant `mm/dd`. Pin the format explicitly.

**Performance drops after adding a cast.** Wrapping a column in `CAST` inside `WHERE` can prevent index use. Compare against a date literal or a computed range instead.

## Common Mistakes

- **Assuming every database has a DATE type.** SQLite does not. `CAST(x AS DATE)` there returns a numeric value, not a formatted date. Test on your actual engine.
- **Casting an ambiguous string without a style.** `'03/04/2024'` can be read two ways. Use `CONVERT(DATE, value, 101)` or an ISO string like `'2024-03-04'` [1].
- **Casting a date to datetime when the year is before 1753.** SQL Server raises an out-of-range error. Use `datetime2` for early dates [1].
- **Forgetting that the time portion is dropped.** `CAST('2024-03-01 23:59:59' AS DATE)` becomes `2024-03-01`, so late-night rows can fall into the wrong day bucket [1].
- **Casting inside a WHERE clause on an indexed column.** This can force a full scan. Filter on a range of raw values when possible.
- **Expecting temporal conversions to be lossless.** MySQL notes that converting between temporal types can alter the value or lose information [3].

## Limitations

A cast changes the type, not the underlying data. If the source string is malformed, the cast fails or silently produces a wrong value depending on the engine. SQL Server raises an error for bad strings, while some engines return `NULL` or a zero date. You cannot rely on one behavior across platforms.

Casting also cannot fix a bad schema. If dates are stored as text, every query that needs date logic must cast, which costs performance and invites format bugs. The durable fix is to store dates in a date or timestamp column and cast only at the edges, when reading external data or formatting output. For arithmetic on dates, see [SQL DATEADD and DATEDIFF: Syntax and Examples](/blog/data-analysis/sql-dateadd-datediff) and [SQL DATEPART Function: Extract Date Parts with Examples](/blog/data-analysis/sql-datepart-function).

## Frequently Asked Questions

### How do I cast a string to a date in SQL?

Use `CAST('2024-03-01' AS DATE)`. The string must match a format your engine recognizes, and ISO 8601 (`yyyy-mm-dd`) is the safest choice. In SQL Server, if the string uses another format, use `CONVERT(DATE, value, style)` with the matching style number [1].

### How do I cast a datetime to a date?

Use `CAST(datetime_column AS DATE)`. The time portion is dropped, leaving only the year, month, and day [1]. This is the standard way to group timestamped rows by calendar day.

### Does CAST work the same in every database?

No. The syntax is similar, but behavior differs. SQL Server, MySQL, and PostgreSQL have real date types. SQLite does not, so `CAST(x AS DATE)` returns a numeric value instead of a formatted date. PostgreSQL also accepts the `::date` shorthand [2].

### How do I convert a date back to a string?

Use `CAST(date_value AS VARCHAR)` or `CONVERT(VARCHAR(10), date_value, 23)` in SQL Server, where style 23 produces `yyyy-mm-dd` [1]. The exact function name varies by engine.

### Why does my cast return NULL or an error?

The input string does not match a recognized format. Check for extra spaces, wrong separators, or a day-month order the server does not expect. Adding an explicit style code usually resolves it [1].

## References

1. [CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver17)
2. [PostgreSQL: Documentation: 18: CREATE CAST](https://www.postgresql.org/docs/current/sql-createcast.html)
3. [MySQL :: MySQL 26.7 Reference Manual :: 13.2.8 Conversion Between Date and Time Types](https://dev.mysql.com/doc/refman/26.7/en/date-and-time-type-conversion.html)
4. [PostgreSQL: Documentation: 18: 9.9. Date/Time Functions and Operators](https://www.postgresql.org/docs/current/functions-datetime.html)

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [SQL CAST as String: Convert Data Types with Examples](/blog/data-analysis/sql-cast-as-string-convert-types)
- [SQL Convert Date: Functions, Formats and Examples](/blog/data-analysis/sql-convert-date)
- [SQL WHERE Date Is Today: Syntax and Examples](/blog/data-analysis/sql-where-date-is-today)
- [SQL CURRENT_DATE: Syntax, Examples and Today's Date Queries](/blog/data-analysis/sql-current-date-function)
- [SQL DATEADD and DATEDIFF: Syntax and Examples](/blog/data-analysis/sql-dateadd-datediff)