TRY_CONVERT

Question

Post reply

TRY_CONVERT

Steve Jones - SSC Editor

SSC Guru

Points: 734444
More actions
February 3, 2016 at 9:55 pm

#100919

Comments posted to this topic are about the item TRY_CONVERT
February 4, 2016 at 1:15 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

Don`t remeber if I ever used this method before, so had to do some search about it, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Good question, this is a function that everyone should know. It reduces the coding required for validating input enormously.

Maybe the question and answers should also have referred to nasty cases like attempting forbidden conversions, which will raise an error rather than returning NULL. Of course when try_convert used for validating input this is unlikely to happen, as no conversions from varchar or nvarchar are forbidden, but in other uses you might get a result likeMsg 529, Level 16, State 2, Line 78

Explicit conversion from data type varbinary to float is not allowed.

Tom

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Easy one, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

One of the good additions to SQL 2012, although I'm still reserving final judgement on it until I do exhaustive performance testing.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Revenant SSC-Forever Points: 42467 More actions · Answer 5

Revenant

SSC-Forever

Points: 42467

February 4, 2016 at 11:31 am

#1856617

Really, really simple. Thanks, Steve!

Eric M Russell SSC Guru Points: 125522 More actions · Answer 6

TRY_CONVERT() is also useful for check constraints. For example, below we are requiring that "date" values stored in a VarChar column called FooDate can be casted as an actual Date.

create table #mytest

(

FooDate varchar(20) not null

constraint cc_FooDate check ( try_convert(date, FooDate) is not null )

);

insert #mytest ( FooDate) values ('2016/02/28');

(1 row(s) affected)

insert #mytest ( FooDate) values ('2016/02/30');

The INSERT statement conflicted with the CHECK constraint "cc_FooDate".

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

Eric M Russell (2/4/2016)
TRY_CONVERT() is also useful for check constraints. For example, below we are requiring that "date" values stored in a VarChar column called FooDate can be casted as an actual Date.
create table #mytest
(
FooDate varchar(20) not null
constraint cc_FooDate check ( try_convert(date, FooDate) is not null )
);
insert #mytest ( FooDate) values ('2016/02/28');
(1 row(s) affected)
insert #mytest ( FooDate) values ('2016/02/30');
The INSERT statement conflicted with the CHECK constraint "cc_FooDate".

Of course, it would be better to store dates in date columns. But that's none of my business. 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125522 More actions · Answer 8

Luis Cazares (2/4/2016)
...
Of course, it would be better to store dates in date columns. But that's none of my business. 😀

Yes, it is best to create the table with real date typed columns in the first place, but sometimes we have to work with what we've got. At least by applying a check constraint we can force the app developer to use an input mask.

developer -> :angry: DBA -> :laugh:

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Ed Wagner SSC Guru Points: 287024 More actions · Answer 9

Eric M Russell (2/4/2016)
Luis Cazares (2/4/2016)
...
Of course, it would be better to store dates in date columns. But that's none of my business. 😀
Yes, it is best to create the table with real date typed columns in the first place, but sometimes we have to work with what we've got. At least by applying a check constraint we can force the app developer to use an input mask.
developer -> :angry: DBA -> :laugh:

Luis is right, of course, but thanks Eric. I never would have thought of using it in a check constraint.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions