Do dates behave differently on different dbs?

Question

I have an asp.net webpage with a jQuery dump.

I am in the UK, so when I enter 28/02/2010 , I expect him to decide on the 28th Feb 2010 .

This works as expected on my local dev env - but not on our QA or prod-like envs - or on one of the other dev machines. In these cases, it seems that an attempt to resolve it to the American date format does not pass the check, because it is out of range.

It seems that jQuery is generating the correct date every time, which makes me think it might be a database problem.

I am using SQL Server 2005, my collation is Latin1_General_CI_AS , my colleagues are using collation SQL_Latin1_General_CP1_CI_AS and Chinese.

Given that we do not control the installation of prod SQL Server (just our db), what is the best way to do this work in a standard way? Change db options or code that uses it?

Thanks in advance! - L

[EDIT to add code information]

This is my code to call datepicker:

  <%=Html.TextBox("DateOfBirth", Model.DateOfBirth.ToShortDateString(), new { @class = "datepicker" })%> 

Here is the js for datepicker:

 DatePickerSettings = { setup: function () { $(".datepicker").datepicker({ dateFormat: 'dd/mm/yy', changeMonth: true, changeYear: true }); } }; 

And here is how I indicate the date in the model:

 [Required] [DisplayName("Date of Birth")] public virtual DateTime DateOfBirth { get; set; } 

The date is displayed correctly in the controller and repository ... until it gets into db.

Thanks:)

Answer 1

Does your page know Culture? You can define UI Cutlure information for different browsers (locales) and the constant culture constant of ASP.NET.

The Culture value determines the results of culture-dependent functions, such as date, number and currency formatting, etc. The UICulture value determines which resources are loaded for the page.

Check out this MSDN link: How to Customize UI Culture and Culture for Globalizing ASP.NET Web Pages

http://msdn.microsoft.com/en-us/library/bz9tc508(v=VS.85).aspx

Answer 2

I was hoping to wait until you update the question with more information, but since I saw some answers suggesting changing the format of the string that you use to talk to the database ...

Do not send dates as source text in SQL queries.

Use a parameterized query, which means you don't have to worry about formatting the value at all. Then you need to make sure that you can correctly set the date format between the browser and ASP.NET.

Among other things, if you include user data in SQL queries directly, you usually open up SQL injection attacks. Always use parameterized queries (unless your web application is a "SQL test tool")

If you are already using parameterized queries, the problem may be between the browser and ASP.NET, and part of the database does not matter. Divide and conquer the problem: chase the data when it goes through different levels (browser, jQuery, ASP.NET, etc.) until you find out where it happened. Don't even think about the fix until you find out where it happened.

Answer 3

Use CONVERT to change the date format to a standard accepted in all environments.

CAST and CONVERT

Answer 4

I will need to see a code that interprets dates in order to know for sure, but the Region and Language parameters on the machines the code runs are a likely suspect. Make sure it is configured for your region.

However, if you cannot change the settings on the servers, you probably should explicitly use CAST or CONVERT in SQL Server to force it to analyze it in a specific region, since you expect the data to be entered.

Answer 5

You also need to check your ASP.Net level and see how it works.

Check the configuration of the machine and make sure they are configured to start in the same date / time / area.

Answer 6

Modify your code to use yyyymmdd format. As far as I know, it works in all databases

Answer 7

To add another opinion here, I find dd / mmm / yyyy the best date format to send to the databases, as it is completely unique across cultures.