Click here to Skip to main content
13,046,184 members (69,509 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


1 bookmarked
Posted 18 Sep 2013

Checking convertability in SQL Server

, 18 Sep 2013
Rate this:
Please Sign up or sign in to vote.
Avoiding conversion errors in scripts using TRY_CONVERT.


We may take a varchar input and to convert it to another data type before inserting/updating to tables. We mostly do an optimistic conversion expecting the varchar value will be in proper convertible format. But this approach can cause exceptions when the input is not in the expected format. 

In SQL Server 2012 a new function has been introduced similar to TryParse in .NET through we can check the convertibility.


Let's say we need to check a value can be converted to float:


Since  4.5 is convertible to float it will return a float with value of 4.5. 

In below example the value is NA and is not convertible.


The output will be NULL in this case. 

By using TRY_CONVERT instead of direct CAST or CONVERT we can avoid conversion errors in the scripts.

Read for details.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Saral S Stalin
Technical Lead
India India
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralMessage Automatically Removed Pin
23-Dec-13 22:00
memberNeo_parimal23-Dec-13 22:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170713.1 | Last Updated 18 Sep 2013
Article Copyright 2013 by Saral S Stalin
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid