Post reply

convert(string > datetime) works but isdate(string) returns 0

  • June 19, 2008 at 9:07 am

    #70863

    hallo

    i'm having troubles in all of the sql server 2000 STD italian. yesterday everything worked fine, now there's a mess with dates:

    some functions converting between strings into dates (italian format ddMMyyyy) are returning "value is

    not a date" error: this because they're expecting a yyyyMMdd format or a MMddyyyy format.

    I tried some, eg.:

    CONVERT(datetime,'13012008',102) returns "gen 13 2008 12:00AM", fine

    ISDATE('13012008') returns 0

    is it possible?

  • June 19, 2008 at 12:56 pm

    #831333

    [font="Arial"]

    The IsDate function does not support the date format entry in the order you presented it.

    -- select ISDATE('13012008')

    -- returns 0

    Instead you should use some thing like this which all return 1's :

    select ISDATE('01/13/2008')

    or

    select ISDATE('01-13-2008')

    Note that the form is MM/DD/YYYY where as you submitted DD/MM/YYYY

    Look up the ISDATE in the help tool and you will see the formats that ISDATE allows.

    Regards,

    Terry

    [/font]

  • June 20, 2008 at 1:03 am

    #831524

    sorry, i misquoted the code i used: the string is not "13012008" but "13-01-2008".

    BTW I found out that the error reside in the fact that the function ISDATE expects a MM/dd/yyyy format date and there are no ways to specify a locale parameter, like in the CONVERT function.

  • June 20, 2008 at 1:33 am

    #831539

    There is a way:

    SET DATEFORMAT dmy

    _____________
    Code for TallyGenerator

  • June 20, 2008 at 1:44 am

    #831548

    thanks!

  • July 19, 2011 at 2:22 am

    #1354651

    ISDATE() uses LANGUAGE and DATEFORMAT options to tell is something a date.

    SET LANGUAGE english

    SELECT ISDATE('2011 jan'), ISDATE('2011 gen') -- yes, no

    SET LANGUAGE italian

    SELECT ISDATE('2011 jan'), ISDATE('2011 gen') -- no, yes

    Language implicitly sets DATEFORMAT. "EXEC sp_helplanguage" and see "dateformat" column.

    DBCC USEROPTIONS -- current settings

    ISDATE works ok only if you have separators between day, month and year, like hyphen, dot, slash etc:

    SET DATEFORMAT dmy

    select isdate('31012011') -- no

    select isdate('31-01-2011') -- yes

    select isdate('31 01 2011') -- no

    select isdate('31.01.2011') -- yes

    select isdate('31/01/2011') -- yes

    SET DATEFORMAT mdy

    select isdate('31012011') -- no

    select isdate('31-01-2011') -- no

    select isdate('31 01 2011') -- no

    select isdate('31.01.2011') -- no

    select isdate('31/01/2011') -- no

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

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

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