Post reply

isdate function

  • January 4, 2010 at 3:56 am

    #100706

    Hi,

    we want to detect the valid date format such that the bolow case:

    1.) '2009-01-32 00:00:00' or similar (invalid month + date)

    1.) '2009-13-01 00:00:00' or similar (invalid month + date)

    2.) '0000-00-00 00:00:00' or similar (00 case)

    3.) include a-z or any special characters

    Can the isdate() function handle all the above exception and return 0(false)? Thanks a lot.

    Best regards,

    Wallace

  • January 4, 2010 at 3:59 am

    #1098329

    Yes Isdate can handle all the above invalid dates and return 0 , it will return 1 for any valid dates.

    This is fairly easy to test in SSMS

  • January 4, 2010 at 5:19 am

    #1098349

    thank you

  • January 4, 2010 at 6:06 am

    #1098363

    Notice that using the IsDate function has some issues. Sometimes it can lead to surprising results as you can see from the small script bellow:

    if isdate(1752) = 1

    select convert(datetime, 1753)

    if isdate ('1753') = 1

    select convert(datetime, 1753)

    select convert(datetime, 1752) as TheDate, isdate(1752)

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 4, 2010 at 6:29 pm

    #1098721

    Does it mean that isdate('1752/01/01 00:00:00') return false while isdate('1753/01/01 00:00:00') return true? (yyyy/mm/dd hh:mm:ss)

  • January 4, 2010 at 9:42 pm

    #1098735

    This is what it means, but this is because January first 1952, is out of range for both smalldatetime and datetime data types, while January first 1953 is not out of range for datetime data type. By the way in my previous message I posted the wrong example (played with it a bit and then copied the wrong part), but you can still see some of the problems. For example, I don’t think that anyone of us will consider an integer number is a date, but the function isdate will return true for many numbers. Another problem is that it sometimes returns false, but if you’ll try to convert that same value that the function showed that it is not a date into a date, you’ll be successful.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

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