SQL CAST as Date: Convert Strings and Datetimes to Dates

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

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

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.

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

Write the cast. The general form is:

CAST(expression AS DATE)

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

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

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

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

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.

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 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 or SQL CURRENT_DATE: Syntax, Examples and Today's Date Queries 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 and SQL DATEPART Function: Extract Date Parts with Examples.

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].