In addition to the existing CAST/CONVERT, new functions are available for data type conversion.
TRY_CONVERT will execute a CONVERT, but it will return NULL instead of raising an error if the conversion cannot be made.
select CONVERT(datetime, ‘Sunday, April 1, 2012 3:00 PM’)
— Msg 241, Level 16, State 1, Line 2
–Conversion failed when converting date and/or time from character string.
select TRY_CONVERT(datetime, ‘Sunday, April 1, 2012 3:00 PM’)
select TRY_CONVERT(datetime, ‘April 1, 2012 3:00 PM’)
If the conversion between two data types isn’t explicitly allowed, an exception will be raised.
select TRY_CONVERT(image, 1)
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to image is not allowed.
According to Books Online, parse should be used to convert from a string to datetime or numeric types. For other types of conversion, CONVERT is recommended.
select PARSE(‘Sunday, April 1, 2012 3:00 PM’ as datetime)
returns a datetime:
The same as parse, but will return a NULL if the conversion can’t be made.
select TRY_PARSE(‘tomorrow, Monday’ as datetime)