2

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

Improve this question
1
  • Your edit seems to be backwards to the question. You asked about getting dates out of the system, but your edit displays you putting dates in - which is entirely different, and btw still shouldn't be using string literals. Commented Nov 26, 2009 at 16:08

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Ok, that can be eliminated...is the code in a sproc that is returning the date - run it in SQL Management studio and see what you get. If you're getting the date as expected format, then the problem is in the front end. Hope this helps HollyStyles.
I already did that. However I did it again this time I connected as the user that ASP.NET uses to connect. and I get a different output. The login has default language set to british english. It's not an NT Account it's a SQl Server login.
2

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).

Improve this answer

2 Comments

It's a SQL Datetime in the select and it remains unchanged, the ADO code uses a SqlAdapter to Fill a DataTable and returns it's default view. I understand on the wire datetimes are seconds since some startdate. I've never had this much trouble with datetimes it's so weird.
In the DataTable, what data-type does the column have? DateTime? or string?
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.