In SQL Server, the TRY_PARSE function can be used to convert a string value to a specified data type, including DATETIME, while providing error handling in cases where the conversion fails. If the conversion is successful, it returns the converted value; if it fails, it returns NULL instead of throwing an error.
Syntax of TRY_PARSE
TRY_PARSE ( expression AS data_type [ USING culture ] )
- expression: The string expression that you want to attempt to convert.
- data_type: The desired data type to convert to (e.g., DATETIME).
- culture: An optional parameter specifying the culture in which to interpret the string.
Example of Using TRY_PARSE
Here's how TRY_PARSE can be used to convert string data to DATETIME values with error handling:
DECLARE @dateString1 NVARCHAR(50) = '2023-10-05';
DECLARE @dateString2 NVARCHAR(50) = 'invalid date';
-- Attempt to parse the first string
SELECT
@dateString1 AS InputString,
TRY_PARSE(@dateString1 AS DATETIME USING 'en-US') AS ParsedDate
UNION ALL
-- Attempt to parse the second string
SELECT
@dateString2 AS InputString,
TRY_PARSE(@dateString2 AS DATETIME USING 'en-US') AS ParsedDate;
Explanation
1. Input Strings: We declare two string variables, one containing a valid date and the other an invalid date.
2. TRY_PARSE Function:
- For @dateString1, TRY_PARSE will successfully convert it to a DATETIME value, resulting in the corresponding date.
- For @dateString2, since the string is not a valid date, TRY_PARSE will return NULL.
Result Set
Executing the above query would yield results like this:
InputString | ParsedDate
------------------|---------------------
2023-10-05 | 2023-10-05 00:00:00.000
invalid date | NULL
Notes
- The TRY_PARSE function is only available in SQL Server 2012 and later.
- If you don’t specify a culture, it will use the default session setting.
- This is particularly useful in data migration tasks, where you may have inconsistent or malformed date formats in your string data. Using TRY_PARSE allows you to handle errors gracefully without disrupting the execution of your SQL code.