Post reply

CONVERT(date,'4/23/2010 0:00',103) fails and ISDATE('4/23/2010 0:00') = 1

  • December 9, 2010 at 5:34 am

    #230412

    Hi, I'm making a procedure which parses Excel worksheet and uses that data to update db.

    I've run into the problem with date field it is contingent on a regional settings of the client computer. To get around this I have a piece of code that basically asks if IsDate('datevalue1')=1

    and if true goes to CONVERT(date,'datevalue1',103)

    Now what I find out during testing is: If reg sett are set to Serbian Latin that the format is '23.4.2010 0:00'

    and that doesn't pass the IsDate check

    If regional settings are English(US) than the format is '4/23/2010 0:00', the IsDate check passes but

    CONVERT fails

    What gives, why is SQL Server behaving in such a way and can anyone suggest any workaround?

    Thanks in advance

  • December 9, 2010 at 6:24 am

    #1260523

    See below sample code. If you still need help, post your code.

    DECLARE @ddmmyyy VARCHAR(MAX), @mmddyyy VARCHAR(MAX)

    SET @ddmmyyy = '23/4/2010 0:00'

    SET @mmddyyy = '4/23/2010 0:00'

    SET DATEFORMAT dmy

    SELECT CASE WHEN Isdate(@ddmmyyy) = 1

    THEN CONVERT(VARCHAR, CAST(@ddmmyyy AS DATETIME), 103)

    ELSE NULL END,

    CASE WHEN Isdate(@mmddyyy) = 1

    THEN CONVERT(VARCHAR, CAST(@mmddyyy AS DATETIME), 103)

    ELSE NULL END

    SET DATEFORMAT mdy

    SELECT CASE WHEN Isdate(@mmddyyy) = 1

    THEN CONVERT(VARCHAR, CAST(@mmddyyy AS DATETIME), 103)

    ELSE NULL END,

    CASE WHEN Isdate(@ddmmyyy) = 1

    THEN CONVERT(VARCHAR, CAST(@ddmmyyy AS DATETIME), 103)

    ELSE NULL END

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • December 9, 2010 at 6:31 am

    #1260529

    Yeah, that's the stuff I was looking for, thank you!

  • December 9, 2010 at 6:43 am

    #1260537

    Dimitrije Mišic (12/9/2010)

    Hi, I'm making a procedure which parses Excel worksheet and uses that data to update db.

    I've run into the problem with date field it is contingent on a regional settings of the client computer. To get around this I have a piece of code that basically asks if IsDate('datevalue1')=1

    and if true goes to CONVERT(date,'datevalue1',103)

    Now what I find out during testing is: If reg sett are set to Serbian Latin that the format is '23.4.2010 0:00'

    and that doesn't pass the IsDate check

    If regional settings are English(US) than the format is '4/23/2010 0:00', the IsDate check passes but

    CONVERT fails

    What gives, why is SQL Server behaving in such a way and can anyone suggest any workaround?

    Thanks in advance

    Format 103 is British/French date format, and there are not 23 months in the year! I think you mean to have US type, so convert using format 101.

    Tom

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

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