SQL Server Datetime issues. American vs. British?

On my test DB, the dates are displayed in a DD/MM/YYYY format. By displayed I mean when you right click, open table in Management Studio, the returned data are displayed in a DD/MM/YYYY format.

Funny thing is, when I write T-SQL to retrieve records, I have to input a MM/DD/YYYY format to get back the right data. Is there anyway I can align this to a DD/MM/YYYY format?

  • SQL Server connection string
  • Database design (SQL CE) in Mvc 4 project using Entity Framework Code First - a few questions
  • Export DBML to SQL script
  • Connecting To Microsoft SQL Server Using TCL
  • Call to undefined function sqlsrv_connect() - Troubleshooting
  • Executing Queries Stored in a Column of a Table
  • 7 Solutions collect form web for “SQL Server Datetime issues. American vs. British?”

    You can use SET LANGUAGE to choose the date format that SQL Server expects in queries (I think management studio uses client computer’s regional settings for display purposes, not sure though). However, I suggest passing values using parameters instead of embedding them in query statement. You won’t encounter any issues if you use parameters. Everything is taken care of.

    set language us_english
    declare @d datetime = '1929/12/18'
    set language british
    declare @d datetime = '1929/12/18' -- fails

    To change the server default language:

    declare @langid int = (select langid from syslanguages where name = 'british')
    exec sp_configure 'default language', @langid
    reconfigure with override

    Personally, I always use YYYY-MM-DD format (or YYYYMMDD) since it’s not culture-specific, and, well, I guess it appeals to me because it’s “logical” (especially when followed by a time).

    [Edit: I’m just talking about what I put in my SQL scripts to ensure compatibility regardless of the server settings, not what SQL Server “displays”]

    You can set the default language for each indvidual SQL Server login. Can’t quite remember, but something like this:

    sp_defaultlanguage @loginame = 'LoginName', @language = 'Language'

    If you pass in DATETIME in the format

    dd MMM yyyy

    for example

    "11 JUL 2009"

    there is never any ambiguity around month and date and therefore you should never have a problem

    In almost all cases, the correct way to solve this is simply to never treat the date as a string. If you pass in a parameter, or use the (typed) column value, then the server’s text conversion simply isn’t a factor. In addition to avoiding the i18n issue, this also reduces your injection attack surface. And it saves a few CPU cycles, too ;-p

    If you are using EXEC for dynamic SQL, then this should likewise be parameterised via sp_ExecuteSQL.

    I try to use the ODBC canonical form of a date wherever possible
    {d ‘yyyy-mm-dd’}
    This way I know how sql server will interpret it.
    It works in TSQL just fine.

    Either add this to your web.config file:

        <globalization culture="en-US" uiCulture="en-US" />

    or you can add this statement on the page:

    <%@ Page uiCulture="en-US" culture="en-US" %>

    Hope this help.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.