SQL Server 2012 contains two new string conversion functions. PARSE and TRY_PARSE. These two functions make life a lot easier when you want to convert a string to another data type.
The different between the two functions are that PARSE will return an error if the conversion fails. TRY_PARSE will return NULL.
Some examples:
<span style="color:green;">/* Returns the same results */
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">PARSE</span><span style="color:gray;">(</span><span style="color:red;">'28 dec 1977' </span><span style="color:blue;">AS date</span><span style="color:gray;">)
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'28 dec 1977' </span><span style="color:blue;">AS date</span><span style="color:gray;">)
</span><span style="color:green;">/* PARSE will return the error
Error converting string value '30 feb 1977' into data type date using culture */
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">PARSE</span><span style="color:gray;">(</span><span style="color:red;">'30 feb 1977' </span><span style="color:blue;">AS date</span><span style="color:gray;">)
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'30 feb 1977' </span><span style="color:blue;">AS date</span><span style="color:gray;">)
</span>
It is possible to tell PARSE and TRY_PARSE if your input string-date is formatted in another regional setting ex. month day year.
<span style="color:green;">/* Returns the same result 3th may 1977 */
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'03-05-1977' </span><span style="color:blue;">AS date USING </span><span style="color:red;">'da-DK'</span><span style="color:gray;">) </span><span style="color:blue;">AS </span><span style="color:teal;">Result
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'05-03-1977' </span><span style="color:blue;">AS date USING </span><span style="color:red;">'en-US'</span><span style="color:gray;">) </span><span style="color:blue;">AS </span><span style="color:teal;">Result
</span>
Finally an example on how to convert a string to an tinyint.
<span style="color:green;">/* The last TRY_PARSE return NULL while 2222 is larger than TINYINT */</span>
<span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'22'</span><span style="color:blue;">AS int</span><span style="color:gray;">)
</span><span style="color:blue;">SELECT </span><span style="color:magenta;">TRY_PARSE</span><span style="color:gray;">(</span><span style="color:red;">'2222'</span><span style="color:blue;">AS tinyint</span><span style="color:gray;">)</span>
In some cases the right thing to do is to raise an error. In that case use PARSE. In most cases I think I’ll go with TRY_PARSE.
Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.
