With SQL Server 2012 comes a new set of conversion functions, that will certainly help doing type casts and conversions in t-sql. These functions are:

• TRY_PARSE
• TRY_CONVERT
• PARSE

TRY_PARSE()

Syntax from BOL:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

This function works similar to the well known CAST, but will return NULL if the string_value does not represent a valid formatted value of the given data type.

Let me show a few examples:

select try_parse('2011-12-24' as date)

This results in the following output, meaning that the value ‘2011-12-24’ is a valid formatted date:

If we try this:

select try_parse('2011-02-30' as date)

The result is NULL as expected, since 30th of february is not a valid date:

Another cool thing is that we can give it a culture, so we can convert date written in different locals. Let me demonstrate:

SELECT TRY_PARSE('11-12-2011' AS datetime USING 'en-US') AS Result
SELECT TRY_PARSE('11-12-2011' AS datetime USING 'da-DK') AS Result

The input value is identical ‘11-12-2011’, but the output is different given the two different locals:

Now it could be tempting to replace all use of CAST to TRY_PARSE, but BOL states that TRY_PARSE should only be used for converting from strings to date/time and number types. Also the function relies on the presence of the .NET CLR, and that the parsing has a small overhead. Never the less it gives you more control of the input formats, as well as the capability of handling wrong formatted input without throwing errors.

PARSE()

Syntax from BOL:

PARSE ( string_value AS data_type [ USING culture ] )

This works pretty much the same as TRY_PARSE, but this function will throw an error if the string_value passed to the function is not a valid representation of the data type. Let me show you the similar parsings as above:

SELECT PARSE('13-12-2011' AS datetime USING 'da-DK') AS Result
SELECT PARSE('13-12-2011' AS datetime USING 'en-US') AS Result

The result is a bit different:

The first results in a date, because the input format is ok for da-DK, but the second one is not, because it means the 13th month. Instead of returning NULL as the TRY_PARSE would do, it throws an error:

(1 row(s) affected)
Msg 9819, Level 16, State 1, Line 2
Error converting string value '13-12-2011' into data type datetime using culture 'en-US'.

TRY_CONVERT()

Syntax from BOL:

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

This is pretty much the same as the well known CONVERT, but again with the NULL output for invalid input strings.

Lets see how the old CONVERT would handle these two:

SELECT CONVERT(XML, '<root>Hello World!</root>') AS MyXML
SELECT CONVERT(XML, '<root>Hello World!) AS MyXML

(Notice the missing </root> element in the second select). The output is this:

The first one converts to XML just fine, but the second doesn’t:

(1 row(s) affected)
Msg 9400, Level 16, State 1, Line 2
XML parsing: line 1, character 18, unexpected end of input

Let’s try the same with the new TRY_CONVERT instead:

SELECT TRY_CONVERT(XML, '<root>Hello World!</root>') AS MyXML
SELECT TRY_CONVERT(XML, '<root>Hello World!') AS MyXML

The output:

And no errors in the message tab.

These are just minor aditions, but I’m sure they will find there use around. Have you ever tried identifying a non-integer value in a varchar column?

Let’s create a dummy table for demonstration:

CREATE TABLE MyBadTableDesign
(
    id INT IDENTITY PRIMARY KEY,
    val varchar(10) --This will hold integer values
)
GO
INSERT INTO MyBadTableDesign (val)
VALUES
    ('1'),
    ('2'),
    ('3'),
    ('4'),
    ('s'),
    ('6'),
    ('t'),
    ('8')
GO

Now we have a table with a val column of type varchar(10). It was supposed to contain only integers (hence my BadTableDesign name…), but unfortunately some non-integer data has entered the table. This gives us a problem, if/when we try to cast the column to an INT:

SELECT id, CAST(val AS INT) AS Val FROM MyBadTableDesign

This gives me this partial result:

And the following error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 's' to data type int.

But what if there were million of rows in the table, and multiple non-integer values? It would not be pleasant to try to identify them all. But with TRY_PARSE we can now do this to identify the rows:

SELECT * FROM MyBadTableDesign
WHERE TRY_PARSE(val AS INT) IS NULL

And this gives us exactly the rows that could not be parsed to an INT

I know I have been in a situation like that before!