February 3, 2016 at 9:55 pm
Comments posted to this topic are about the item TRY_CONVERT
February 3, 2016 at 11:00 pm
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
February 4, 2016 at 1:15 am
This was removed by the editor as SPAM
February 4, 2016 at 4:16 am
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
February 4, 2016 at 4:36 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 4, 2016 at 5:27 am
One of the good additions to SQL 2012, although I'm still reserving final judgement on it until I do exhaustive performance testing.
February 4, 2016 at 11:31 am
Really, really simple. Thanks, Steve!
February 4, 2016 at 11:44 am
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
February 4, 2016 at 11:48 am
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. 😀
February 4, 2016 at 1:26 pm
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
February 4, 2016 at 1:43 pm
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.
February 8, 2016 at 9:18 am
Thanks. Learned something new.
March 8, 2016 at 4:29 pm
Never used this before.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply