Error: The conversion of char data type to smalldatetime...

iposneriposner Posts: 6
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

SQL 2005, British English connection using SQL Data Compare 7.1.0.245.

Changing my default language to English makes the problem go away.

Looks like it's that old chestnut of coding with US date format strings rather than ISO format strings (yyyymmdd) which would make the problem go away... :wink:
Ian Posner
Director
MindQuest Solutions Ltd

Comments

  • I have exactly the same problem.

    I'm disapointed that the tool can't cope with non-US date formats.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Thanks for your post. As far as I know, though, date comparisons are culture specific according to the location set on your operating system.

    The problem may be an incompatibility between the date format returned by SQL Server being misinterpreted when converted to a .NET DateTime type.

    I'll see if I can get a clear answer on this from our development team.

    We definitely don't force you to use a US datetime format, or we couldn't use our own software as we are a UK company!
  • The culture specific comparisons in .NET have nothing to do with the SQL Server default language. In the reported bug scenario, the user sesson on the workstation was UK english, but the default language for the connection was British English. When the SQL Server default language for the connection was changed to English (US), everything worked.

    Most people don't realise it - regardless of the local workstation settings, they connect to SQL Server using the default English (US) language, so don't experience the problem.
    Ian Posner
    Director
    MindQuest Solutions Ltd
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    If you run SQL Profiler during a Data Compare, you can see that Data Compare is explicitly setting the following settings on the connection:
    -- network protocol: LPC
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    Can you run Profiler and see if you get something different? I'd assume that explicitly setting us_english and dateformat mdy would return datetime strings in a consistent format.

    Also, is this happening during comparison or synchronization of data?
  • If Data Compare is explicitly seeting the language during the compare, then this is the cause of the problem.

    I am generating the scripts from data compare, but then pasting these scripts into our own rollout tool (which runs sqlcmd).

    So the scripts are being created in us_english, but then when they are run against the target database they are being run using the language of british (which our db is set to use).

    As a workaround I am now setting the language to us_english at the begining of the script, and then back again at the end.

    I think it would make scense if there was an option to "Use the database language" or "Specify language". If a language is specified then it should also be included in the script output to ensure it runs correctly.

    Thanks
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    For my clarification, is the workaround to put the text

    SET LANGUAGE us_English

    at the top of the SQL Script that Data Compare produces, or to actually change the language on the server?
  • For my clarification, is the workaround to put the text

    SET LANGUAGE us_English

    at the top of the SQL Script that Data Compare produces, or to actually change the language on the server?

    At the top of the sql script that data compare produces.
    And then set it back to british at the bottom of the script
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Thanks--I have added a suggestion that we put SET LANGUAGE us_english in the synch script.

    One of our managers will evaluate the request and put it in the product if it solves these problems and there isn't a better way of doing it.
Sign In or Register to comment.