SQL Server Date Styles: A Comprehensive Guide to CAST and CONVERT Functions

In SQL Server, transforming data types is a common task, especially when dealing with dates and times. The CAST and CONVERT functions are your go-to tools for this, offering a wide array of formatting options known as Sql Server Date Styles. Whether you need to display dates in a specific format for reports, ensure data compatibility, or manipulate date values for calculations, understanding these styles is crucial.

This guide provides an in-depth look at SQL Server date styles, exploring how to use them effectively with the CAST and CONVERT functions. We’ll delve into the various date and time styles available, their syntax, and provide practical examples to help you master date formatting in SQL Server. By the end of this article, you’ll be equipped to confidently handle any date and time conversion scenario, ensuring your SQL queries are both efficient and produce the desired output.

Understanding CAST and CONVERT Syntax

Before diving into specific date styles, let’s clarify the basic syntax of the CAST and CONVERT functions. Both functions serve the purpose of converting an expression from one data type to another, but they differ slightly in their syntax and capabilities, particularly when dealing with date styles using CONVERT.

CAST Syntax

The CAST function follows a straightforward syntax:

CAST ( expression AS data_type [ ( length ) ] )

expression: The value you want to convert. This can be a column name, a literal value, or another expression.
data_type: The target data type you want to convert the expression to. For date styles, you’ll often be converting to character data types like VARCHAR or NVARCHAR to display dates in a specific format.
length (optional): Specifies the length of the target data type, applicable for data types that allow length specification.

CONVERT Syntax

The CONVERT function offers more flexibility, especially when formatting dates, with the inclusion of the style argument:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

data_type: The target data type, similar to CAST.
length (optional): Length of the target data type, if applicable.
expression: The value to be converted.
style (optional): An integer expression that dictates how CONVERT should translate the expression, especially for date and time conversions. This is where SQL Server date styles come into play.

While CAST is ANSI-SQL standard and generally preferred for data type conversions, CONVERT is specific to Transact-SQL and offers the crucial style argument for date formatting. For working with SQL Server date styles, CONVERT is often the more powerful and direct choice.

SQL Server Date and Time Styles: A Comprehensive Table

The CONVERT function utilizes style codes to format date and time data types. These styles determine the output string format when converting date and time values to character data. Here’s a detailed table outlining the most commonly used SQL Server date styles:

Style	Without Century (yy)	With Century (yyyy)	Standard	Input/Output Format	Description
0	–	100	Default for datetime and smalldatetime	`mon dd yyyy hh:miAM` (or `PM`)	Default format, includes month name, day, year, and time
1	1	101	U.S.	`mm/dd/yy` / `mm/dd/yyyy`	U.S. date format with month/day/year
2	2	102	ANSI	`yy.mm.dd` / `yyyy.mm.dd`	ANSI standard date format with year.month.day
3	3	103	British/French	`dd/mm/yy` / `dd/mm/yyyy`	British/French date format with day/month/year
4	4	104	German	`dd.mm.yy` / `dd.mm.yyyy`	German date format with day.month.year
5	5	105	Italian	`dd-mm-yy` / `dd-mm-yyyy`	Italian date format with day-month-year
6	6	106	–	`dd mon yy` / `dd mon yyyy`	Day, month abbreviation, year format
7	7	107	–	`Mon dd, yy` / `Mon dd, yyyy`	Month abbreviation, day, year format
8	8	108	–	`hh:mi:ss`	Time format (hours:minutes:seconds)
9	–	109	Default + milliseconds	`mon dd yyyy hh:mi:ss:mmmAM` (or `PM`)	Default with milliseconds
10	10	110	USA	`mm-dd-yy` / `mm-dd-yyyy`	U.S. date format with month-day-year
11	11	111	JAPAN	`yy/mm/dd` / `yyyy/mm/dd`	Japanese date format with year/month/day
12	12	112	ISO	`yymmdd` / `yyyymmdd`	ISO date format (yyyymmdd – no separators)
13	–	113	Europe default + milliseconds	`dd mon yyyy hh:mi:ss:mmm` (24-hour)	European default with milliseconds (24-hour format)
14	14	114	–	`hh:mi:ss:mmm` (24-hour)	Time with milliseconds (24-hour format)
20	–	120	ODBC canonical	`yyyy-mm-dd hh:mi:ss` (24-hour)	ODBC canonical date and time format (24-hour format)
21	–	121	ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset	`yyyy-mm-dd hh:mi:ss.mmm` (24-hour)	ODBC canonical with milliseconds (24-hour format)
22	22	–	U.S.	`mm/dd/yy hh:mi:ss AM` (or `PM`)	U.S. date and time format with seconds
23	–	23	ISO8601	`yyyy-mm-dd`	ISO8601 date format (yyyy-mm-dd)
24	8	108	–	`hh:mi:ss`	Time format (hours:minutes:seconds)
25	–	121	ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset	`yyyy-mm-dd hh:mi:ss.mmm` (24-hour)	ODBC canonical with milliseconds (24-hour format)
126	–	126	ISO8601	`yyyy-mm-ddThh:mi:ss.mmm` (no spaces)	ISO8601 format for XML (no spaces)
127	–	127	ISO8601 with time zone Z	`yyyy-MM-ddThh:mm:ss.fffZ` (no spaces)	ISO8601 with time zone (UTC)
130	–	130	Hijri	`dd mon yyyy hh:mi:ss:mmmAM`	Hijri date format (Kuwaiti algorithm)
131	–	131	Hijri	`dd/mm/yyyy hh:mi:ss:mmmAM`	Hijri date format with numeric month

Key Considerations for Date Styles:

Century: Styles with 100 prefix include the century (yyyy), while those without (or single-digit styles) may or may not include the century depending on SQL Server’s two-digit year cutoff configuration. It’s best practice to use century-inclusive styles (100+) for clarity.
Default Styles: Styles 0, 9, 13, 20, 21 are default styles and always return the century (yyyy).
ISO 8601: Styles 23, 126, and 127 are ISO 8601 compliant, important for data exchange and XML.
Localization: Some styles are culturally specific (U.S., British/French, German, Italian, Japanese, Hijri), catering to different regional date formats.
Time Zones: Style 127 includes time zone information (Z for UTC), crucial for applications dealing with global time.
Nondeterministic Styles: Styles below 100 (except 20 and 21), and styles 106, 107, 109, 113, 130 are considered nondeterministic. This means their output can vary based on server settings (like language or date format settings). For consistent and predictable results, it’s recommended to use deterministic styles (100+ and styles 20, 21, 23, 126, 127).

Alt text: SQL Server Data Type Conversions: A visual chart illustrating explicit and implicit data type conversions supported in SQL Server, including date, time, numeric, string, binary, and XML data types.

This diagram from the original documentation provides a visual representation of data type conversions in SQL Server, further emphasizing the context in which CAST and CONVERT functions are used. Understanding these allowed conversions can help in choosing the right function and style for your specific date formatting needs.

Practical Examples of SQL Server Date Styles

Let’s explore practical examples to illustrate how to use CONVERT with different SQL Server date styles. We’ll use the GETDATE() function to retrieve the current date and time and format it using various styles.

Example 1: Default Date and Time (Style 0 and 100)

SELECT
    GETDATE() AS UnconvertedDateTime,
    CONVERT(VARCHAR, GETDATE(), 0) AS Style0,
    CONVERT(VARCHAR, GETDATE(), 100) AS Style100;

This example demonstrates the default style (0 or 100) for datetime and smalldatetime. Both styles produce the same output, showing the month name, day, year, and time in AM/PM format.

Example 2: U.S. Date Format (Style 1 and 101)

SELECT
    GETDATE() AS UnconvertedDateTime,
    CONVERT(VARCHAR, GETDATE(), 1) AS Style1,
    CONVERT(VARCHAR, GETDATE(), 101) AS Style101;

Styles 1 and 101 present the date in the U.S. format (mm/dd/yy or mm/dd/yyyy). Style 101 includes the century, making it a more robust choice.

Example 3: ISO 8601 Date Format (Style 23 and 126)

SELECT
    GETDATE() AS UnconvertedDateTime,
    CONVERT(VARCHAR, GETDATE(), 23) AS Style23,
    CONVERT(VARCHAR, GETDATE(), 126) AS Style126;

Styles 23 and 126 are crucial for ISO 8601 compliance. Style 23 provides just the date part (yyyy-mm-dd), while style 126 includes time with milliseconds and a ‘T’ separator, ideal for XML and web services.

Example 4: British/French Date Format (Style 3 and 103)

SELECT
    GETDATE() AS UnconvertedDateTime,
    CONVERT(VARCHAR, GETDATE(), 3) AS Style3,
    CONVERT(VARCHAR, GETDATE(), 103) AS Style103;

Styles 3 and 103 format the date according to British/French conventions (dd/mm/yy or dd/mm/yyyy).

Example 5: Time Only Format (Style 8 and 108)

SELECT
    GETDATE() AS UnconvertedDateTime,
    CONVERT(VARCHAR, GETDATE(), 8) AS Style8,
    CONVERT(VARCHAR, GETDATE(), 108) AS Style108;

Styles 8 and 108 extract only the time portion from a datetime value, displaying it in hh:mi:ss format.

Example 6: ODBC Canonical Format (Style 20 and 120)

SELECT
    GETDATE() AS UnconvertedDateTime,
    CONVERT(VARCHAR, GETDATE(), 20) AS Style20,
    CONVERT(VARCHAR, GETDATE(), 120) AS Style120;

Styles 20 and 120 represent the ODBC canonical format (yyyy-mm-dd hh:mi:ss), useful for data exchange and interoperability.

Example 7: Hijri Date Format (Style 130 and 131)

SELECT
    GETDATE() AS UnconvertedDateTime,
    CONVERT(NVARCHAR, GETDATE(), 130) AS Style130,
    CONVERT(NVARCHAR, GETDATE(), 131) AS Style131;

Styles 130 and 131 format dates according to the Hijri calendar (using the Kuwaiti algorithm). Note that style 130 uses month names in Arabic Unicode, which might not render correctly in all environments without proper font support.

These examples demonstrate just a fraction of the available SQL Server date styles. By experimenting with different style codes within the CONVERT function, you can achieve a wide variety of date and time output formats to suit your specific requirements.

Choosing the Right SQL Server Date Style

Selecting the appropriate SQL Server date style depends on several factors:

Target Audience/Region: If your reports or applications are for a specific region, choose a style that aligns with their date format conventions (e.g., U.S., European).
Data Exchange Standards: For data exchange with other systems or applications, especially in web services or XML contexts, ISO 8601 styles (23, 126, 127) are often the best choice for interoperability.
Readability: For human-readable reports, styles with month names or clear separators (like style 107 – Mon dd, yyyy) can enhance clarity.
Sorting and Filtering: If you need to sort or filter date strings, ISO 8601 formats (yyyy-mm-dd) are ideal because they sort chronologically as strings.
Deterministic vs. Nondeterministic: For mission-critical applications or stored procedures where consistent output is paramount, favor deterministic styles (100+ and styles 20, 21, 23, 126, 127) to avoid unexpected formatting changes due to server settings.
Specific Requirements: Sometimes, you might have very specific formatting needs, like extracting only the time, or displaying milliseconds. In these cases, explore the full range of styles to find the one that best matches.

By carefully considering these factors, you can select the most appropriate SQL Server date style for each situation, ensuring your date and time data is presented accurately and effectively.

Beyond Date Styles: Other Considerations with CAST and CONVERT

While this article focuses on SQL Server date styles, it’s important to remember that CAST and CONVERT are versatile functions for data type conversion in general. They are also used for:

Numeric Conversions: Converting between integer, decimal, float, and money data types. CONVERT also offers styles for money and float data types to control formatting (e.g., adding commas, decimal places, scientific notation).
String Conversions: Converting between character data types (CHAR, VARCHAR, NCHAR, NVARCHAR) and binary data types (BINARY, VARBINARY). CONVERT styles in binary conversions can control the inclusion of ‘0x’ prefix and hexadecimal representation.
XML Conversions: Converting strings to XML data types and vice versa. CONVERT styles for XML control whitespace handling and DTD processing.
Large Value Data Types: CAST and CONVERT work with large-value data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) similar to their smaller counterparts.

Understanding the full scope of CAST and CONVERT empowers you to handle diverse data transformation tasks within SQL Server, making your queries more flexible and robust.

Conclusion

Mastering SQL Server date styles with CAST and CONVERT is essential for anyone working with date and time data in SQL Server. By understanding the syntax of these functions and the wide range of available style codes, you gain precise control over date formatting, ensuring your data is presented in the desired format for reports, applications, and data exchange. Remember to choose styles based on your specific needs, considering regional conventions, data exchange standards, readability, and determinism. With practice and a good understanding of these concepts, you’ll be able to confidently handle any date and time formatting challenge in SQL Server.

Next Steps

Experiment with different SQL Server date styles using the CONVERT function and GETDATE() to observe the output formats.
Practice using CAST and CONVERT in your SQL queries to format date columns for reports and applications.
Explore the styles available for numeric, binary, and XML conversions within the CONVERT function to broaden your data transformation skills.
Review the official SQL Server documentation for CAST and CONVERT for a comprehensive understanding of all features and options.