TRY_CONVERT

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715979

    Comments posted to this topic are about the item TRY_CONVERT

  • Hany Helmy

    SSChampion

    Points: 13321

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

  • This was removed by the editor as SPAM

  • TomThomson

    SSC Guru

    Points: 104772

    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: 258942

    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: 286958

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

  • Revenant

    SSC-Forever

    Points: 42467

    Really, really simple. Thanks, Steve!

  • Eric M Russell

    SSC Guru

    Points: 125020

    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: 183571

    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: 125020

    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: 286958

    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.

  • bgodick

    SSC-Addicted

    Points: 459

    Thanks. Learned something new.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Never used this before.

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

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