Post reply

ISDATE

  • August 28, 2012 at 7:41 am

    #1530802

    Good question, was also misdirected by BOL but kept my option and got it right.

    I also +1 to Hugo's submission, I hope Microsoft fixes it quick.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • August 28, 2012 at 9:24 am

    #1530889

    Hugo Kornelis (8/28/2012)

    An interesting question - especially because the correct answer (and it is correct, as can be easily proven by running the code) is not what the documentation predicts.

    The documentation on ISDATE (linked in the answer explanation) says: "expression -- Is a character string or expression that can be converted to a character string."

    The documentation on converting (http://msdn.microsoft.com/en-us/library/ms187928.aspx) says that implicit conversions from date to character strings (char, varchar. nchar, and nvarchar) are allowed. As can also be easily verified by running the code below.

    So according to the documentation, the ISDATE should convert the date to character string, then test if it's a valid date. Just as it does, and always has done, when fed a datetime argument.

    The text of the error message indicates that this is a deliberate change of behaviour between the date data type and the old datetime data type, so my guess is that this is a documentation bug.

    EDIT: Forgot to paste in the demo code...

    -- Show that date will implicitly convert to string

    DECLARE @dt1 DATE = '20120828';

    SELECT RTRIM(@dt1);

    go

    -- Show that datetime will work in ISDATE (even on SQL 2008 and SQL 2012)

    DECLARE @dt1 DATETIME = '20120828';

    SELECT ISDATE(@dt1);

    go

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 28, 2012 at 10:44 am

    #1530953

    Great question. It seems counterintuitive that ISDATE would not work for a date data type since it works for datetime. But I am not sure why you would ever need to do that.

  • August 29, 2012 at 3:28 am

    #1531282

    Thanks all for the great question and other interesting forum posts.

    Makes a good read.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • September 2, 2012 at 10:39 am

    #1532748

    Koen Verbeeck (8/28/2012)

    Hugo Kornelis (8/28/2012)

    I've raised a documentation bug for this issue: https://connect.microsoft.com/SQLServer/feedback/details/759944/documentation-of-isdate-function-incorrect

    Voted +1.

    (and indicated I could reproduce it)

    Very interesting question, thanks.

    +1

    Tom

Viewing 5 posts - 16 through 19 (of 19 total)

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