Keywords: SQL Server | Date Conversion | CONVERT Function | datetime | String Format
Abstract: This article provides an in-depth exploration of the CONVERT function's application in date string conversion within SQL Server, focusing on the distinction between style parameters 103 and 111. Through concrete examples, it demonstrates how to properly convert 'dd/mm/yyyy' formatted strings to datetime type, avoiding common 'out-of-range value' errors, and offers a comprehensive reference table of date-time styles along with usage recommendations.
Problem Background and Error Analysis
In SQL Server development, format conversion of date strings is a common yet error-prone operation. When users attempt CONVERT(datetime, '23/07/2009', 111), they encounter the error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value," whereas CONVERT(datetime, '07/23/2009', 111) executes successfully.
Core Role of Style Parameter
The third parameter of the CONVERT function—style—determines the parsing format of the string. According to Microsoft official documentation, style 111 corresponds to the Japanese standard format yyyy/mm/dd, meaning the string must be arranged in "year/month/day" order. Thus, '07/23/2009' is correctly parsed as July 23, 2009, while '23/07/2009' fails conversion due to format mismatch.
Correct Solution
For date strings in dd/mm/yyyy format, style 103 should be used, which corresponds to the British/French standard format:
SELECT CONVERT(datetime, '23/07/2009', 103)This code will successfully convert the string to datetime type, resulting in July 23, 2009.
Detailed Explanation of Date-Time Styles
SQL Server offers a rich set of date-time conversion styles. Here are some commonly used styles and their corresponding formats:
- 101 (U.S.):
mm/dd/yyyy - 102 (ANSI):
yyyy.mm.dd - 103 (British/French):
dd/mm/yyyy - 104 (German):
dd.mm.yyyy - 105 (Italian):
dd-mm-yyyy - 111 (Japan):
yyyy/mm/dd - 112 (ISO):
yyyymmdd - 120 (ODBC canonical):
yyyy-mm-dd hh:mi:ss
Comparison of CONVERT and CAST Functions
SQL Server provides two type conversion functions:
- CAST function: Basic syntax is
CAST(expression AS data_type) - CONVERT function: Syntax is
CONVERT(data_type, expression [, style])
The advantage of the CONVERT function lies in its support for style parameters, enabling handling of specific date-time string formats, whereas the CAST function is suitable for simple data type conversions.
Practical Application Examples
The following examples demonstrate conversion methods for date strings in different formats:
-- U.S. format conversion
SELECT CONVERT(datetime, '07/23/2009', 101) AS US_Format
-- ISO format conversion
SELECT CONVERT(datetime, '20090723', 112) AS ISO_Format
-- German format conversion
SELECT CONVERT(datetime, '23.07.2009', 104) AS German_FormatError Prevention and Best Practices
To avoid date conversion errors, it is recommended to:
- Always explicitly specify the style parameter to avoid relying on default settings
- Use four-digit years to prevent ambiguity with two-digit years
- Perform thorough format validation in production environments
- Consider using TRY_CONVERT function for error handling
Extended Application Scenarios
Date-time conversion is not limited to string-to-datetime conversion but also includes:
- Output conversion from datetime to various string formats
- Conversion between different date-time types (e.g., date, time, datetime2)
- Handling of timezone-related datetimeoffset type
- Batch conversion applications in stored procedures and functions
By mastering the usage of style parameters in the CONVERT function, developers can efficiently handle various international date formats, ensuring global compatibility of database applications.