Post reply

Which database setting controls how a datetime string is parsed?

  • March 30, 2009 at 2:55 am

    #132307

    Hi, I hope this is the correct forum for my question - I feel pretty sure that a db admin would know the answer.

    I have a production and a test environment as well as a local development sql server. I really want my test database environment to behave in exactly the same way as my development and production environments. But I don't know what setting I need to change (or whether I need to change the windows server configuration or the sql server configuration)

    I'm not sure which regional settings i can tell you that will help. But all 3 machines are a mixture of english and norwegian.

    Here is a distillation of my problem:

    Say I'd like to perform a simple query

    SELECT firstname, birthday FROM employees WHERE birthday = '1980-03-28'

    When I enter this query from server management studio on my localDev machine against my localDev database server I get a useful result.

    The same query from mgmtStudio on localDev against the test database gives a useful result.

    However the same query from sqlMgmtStudio on the test db server against the test database returns an error.

    "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

    Develpment against production and production against production queries are also fine.

    What settings do i need to look at to correct this? Just aski if theres any more info that would help in solving this.

    Thanks in advance!

  • March 30, 2009 at 6:17 am

    #968245

    Hi again - just to answer my own question, I've now learnt the following:

    Using the following query to find the language settings:

    SELECT @@language, @@langid

    Resultater:

    Development: us_english,0

    Test: norsk,8

    Production: us_english,0

    Then a little test on all 3 machines

    SET LANGUAGE us_english;

    GO

    SELECT firstname, birthday FROM employees WHERE birthday = '1980-03-28'

    SET LANGUAGE norsk;

    GO

    SELECT firstname, birthday FROM employees WHERE birthday = '1980-03-28'

    On all 3 setups, the first query gave a result, while the second gave an error.

    So I'd found the critical difference.

    Then I started thinking about the user settings, for all the different connection scenarios.

    Query from dev client -> dev server - windows authentication (my user) ok

    Query from dev client -> prod server - server authentication (my sql user name) ok

    Query from dev client -> test server - server authentication (sa) ok

    Query from test client -> test server - windows authentication (testSQLUser) *formatting error*

    Query from prod client -> prod server - windows authentication (prodSQLUser) ok

    The bottom line - I just had to change the default language for the windows authented user on the test database from norwegian to english - after that, it went fine.

    The second thing I've found out is that if you want to express date as a string, the best format to use is 'yyyyMMdd' as this should work for all languages.

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

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