How to Convert String to Date in SQL Server

Galaxy Glossary

How do I convert a string to DATE or DATETIME in SQL Server?

The CONVERT, CAST, and PARSE/TRY_PARSE functions turn a character string into a DATE, DATETIME, or DATETIME2 value in SQL Server.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What is the quickest way to turn a string into a DATE?

Use CAST or CONVERT when the input string is in an unambiguous ISO format like ‘2024-06-18’. Both functions succeed without specifying a style number.

How does the CONVERT function work?

CONVERT(data_type, expression, style) changes the data type of expression. The optional style argument tells SQL Server how to interpret the string (e.g., 101 = ‘mm/dd/yyyy’).

Example: U.S. date string

SELECT CONVERT(date, '06/18/2024', 101) AS order_date;

When should I use CAST instead of CONVERT?

CAST(data_type AS target_type) is ANSI-SQL and portable. Use it when the string is already ISO-8601 or when you do not need a style code.

Example: ISO string from Orders table

SELECT CAST(order_date AS date) FROM Orders;

How do I handle multiple date formats safely?

TRY_CONVERT and TRY_PARSE return NULL when the conversion fails, preventing runtime errors in reports and data pipelines.

Example: TRY_CONVERT with fallback

SELECT TRY_CONVERT(date, shipped_at, 103) -- 'dd/mm/yyyy' , CASE WHEN TRY_CONVERT(date, shipped_at, 103) IS NULL THEN 'Bad Format' END AS warning FROM Orders;

Which style codes cover common ecommerce data?

101 = U.S. ‘mm/dd/yyyy’, 102 = ANSI ‘yyyy.mm.dd’, 103 = U.K. ‘dd/mm/yyyy’, 126 = ISO8601 ‘yyyy-mm-ddThh:mi:ss’ (web APIs).

How can I bulk-update a VARCHAR column to DATE?

UPDATE Orders SET order_date = CONVERT(date, order_date_varchar, 126); Always test with SELECT first to avoid data loss.

Best practices for converting strings to dates

Store dates in DATE or DATETIME2, validate input at the application layer, prefer ISO-8601 strings, and wrap risky conversions in TRY_CONVERT.

Why How to Convert String to Date in SQL Server is important

How to Convert String to Date in SQL Server Example Usage


-- Convert string ship dates when joining Orders and OrderItems
SELECT o.id,
       CONVERT(date, o.shipped_at_txt, 126)  AS shipped_date,
       SUM(oi.quantity * p.price)            AS revenue
FROM   Orders       o
JOIN   OrderItems   oi ON oi.order_id   = o.id
JOIN   Products     p  ON p.id          = oi.product_id
WHERE  TRY_CONVERT(date, o.shipped_at_txt, 126) >= '2024-01-01'
GROUP  BY o.id, CONVERT(date, o.shipped_at_txt, 126);

How to Convert String to Date in SQL Server Syntax


-- Basic forms
CAST ( expression AS data_type )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
PARSE ( expression AS data_type [ USING culture ] )
TRY_CONVERT / TRY_PARSE return NULL on failure.

-- Ecommerce examples
-- 1. Import U.S. formatted order dates
SELECT CONVERT(date, '06/18/2024', 101);

-- 2. Validate mixed date strings during ETL
SELECT TRY_CONVERT(datetime2, order_date_txt, 126)
FROM Orders_STG;

-- 3. Parse culture-specific product launch dates
SELECT PARSE('18.06.2024' AS date USING 'de-DE');

Common Mistakes

Using CAST/CONVERT without a style code for non-ISO strings. SQL Server may misinterpret ‘01/04/2024’ as April 1 in U.S. servers but January 4 in U.K. deployments. Always supply the right style code (103 for U.K.).
Relying on PARSE in high-volume ETL jobs. PARSE uses the .NET CLR and is much slower than CONVERT. Prefer CONVERT or TRY_CONVERT for large datasets.

Frequently Asked Questions (FAQs)

Is TRY_CONVERT available in all SQL Server versions?

TRY_CONVERT and TRY_PARSE were introduced in SQL Server 2012. For older versions, simulate with CASE and ISDATE().

What data type should I store—DATE or DATETIME2?

Use DATE for calendar dates without time. Choose DATETIME2(3) or higher for timestamps; it offers more precision and a larger range than DATETIME.

Can I set a default culture for PARSE?

No. You must pass the culture each time or rely on the server’s regional settings, which is discouraged for consistency.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie