TRY_CONVERT

  • Comments posted to this topic are about the item TRY_CONVERT

  • 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

  • This was removed by the editor as SPAM

  • 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

  • Easy one, thanks.

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

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

  • Really, really simple. Thanks, Steve!

  • 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

  • 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
  • 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

  • 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.

  • Thanks. Learned something new.

  • 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