.NET DateTime object string format

Question

This has got me really flumoxed!

1. In the datalayer ADO.NET connects to SQL Server 2008, Default language for the login is 'british'
2. Selects a DateTime column into a dataview and returns it.
3. aspx page databinds
4. this: <%# String.Format("{0:MMM/yyyy}", Eval("dbPeriodFrom")) %>

The database returns 2009/10/01 (This is yyyy/MM/dd) The result of step 4 is Jan2009 ????

The regional settings of the web server is United Kingdom There is no <globalization... section in machine.config The NET globalisation in IIS is set to uiCulture=en culture=en-GB I even set it in the web.config for the site

This is a classic "Works on my dev machine.." But, borked when deployed to production scenario. What could I possibly have missed?

EDIT

So it appears the login used by the ASP.NET Application to connect to SQl Server 2008 is getting a US datetime, even though in the properties for the login, the default language is set to 'British English'.

The problem occurs in TSQL:

SELECT 
    DATEPART(month, CAST('2009.02.01' AS DATETIME))
    ,DATEPART(month, CONVERT(DATETIME, '2009.02.01', 102))

OUTPUT for windows integrated login (Administrator) with default language set to 'English'

2 2

OUTPUT for SQL Server login used by ASP.NET with default language set to 'British English'

1 2

Answer 1

I'd be interested in seeing the code where you get the date out of the command/reader/adapter - if the database column is typed as a datetime, then what comes over the wire isn't actually "2009/10/01" - it is a binary number (like most dates are on the wire). As such there is no ambiguity.

I expect that somewhere you are treating it as a string (perhaps some Parse) - this shouldn't be necessary. If it is, you aren't SELECTing it as a datetime, but as a [n][var]char(x).

Answer 2

Check the locale setting on the database itself and on their server, depending on the set up, the date will be formatted accordingly to the locale setting. I suspect the database server is probably set up to US English - Poke around in the regional settings on the db server itself.

Hope this helps, Best regards, Tom.

Answer 3

Check value stored in database using Management Studio. Also in MS SQL server universal format of date is YYYY-MM-DDTHH:MM:SS.mmm (2009-01-05T10:12:55.001) and YYYYMMDD (20090105). Those formats parsed always the same, no matter which locale used by database.