ASP.NET将不一致的DateTime格式传递给SQL Server

I know this is an age-old question but I'm not exactly new to it and I haven't found an answer which helps me so far!

我知道这是一个古老的问题,但我并不是新手,我还没有找到答案,这对我有帮助!

I've got an ASP.NET site which displays data from an SQL Server 2005 database. At the top of the page are 'Date From' and 'Date To' textboxes which are used to filter the data shown beneath.

我有一个ASP.NET站点,它显示来自SQL Server 2005数据库的数据。页面顶部是“Date From”和“Date To”文本框,用于过滤下面显示的数据。

The data is displayed using an SqlDataSource, and the two dates are passed as parameters to a Stored Procedure. The textboxes display the dates and accept input in UK date format (dd/MM/yyyy) and it works fine.

使用SqlDataSource显示数据,并将两个日期作为参数传递给存储过程。文本框显示日期并接受英国日期格式(dd / MM / yyyy)的输入,它工作正常。

Now I've added a new page with exactly the same setup, displaying slightly different data. In the backend I created a new Stored Procedure by copying and pasting my original one, it's almost identical. And yet on this page I get errors with my dates because they're being read as MM/dd/yyyy, meaning that today's date, for example, 15th August 2011, is passed as 15/08/2011 and isn't a valid date.

现在我添加了一个具有完全相同设置的新页面,显示略有不同的数据。在后端,我通过复制和粘贴原始存储过程创建了一个新的存储过程,它几乎完全相同。然而在这个页面上,我的日期出错了,因为它们被读作MM / dd / yyyy,这意味着今天的日期,例如,2011年8月15日,将于2011年8月15日通过,并且无效日期。

I've checked over everything and I can't understand why this should work on one page and not another, especially when I've basically just copied all of the original code and tweaked it slightly. Can anyone suggest anything I can check that I might not have thought of?

我检查了一切,我无法理解为什么这应该在一个页面而不是另一个页面上工作,特别是当我基本上只是复制了所有原始代码并稍微调整它时。任何人都可以建议我可以检查一下我可能没有想到的吗?

3 个解决方案

#1

The text boxes are strings, which are in the thread local which is set from the browser (langauges).

文本框是字符串,它们位于从浏览器设置的本地线程中(langauges)。
Convert them to aa DateTime object first, using a local aware transformation, then write the dateTime object to the server. NEVER (!) deal with strings to sql server unless you format them in ISO independent form (2011-08-17 23:52:11).

首先使用本地感知转换将它们转换为DateTime对象,然后将dateTime对象写入服务器。除非你用ISO独立形式(2011-08-17 23:52:11)格式化它们,否则永远不会(!)处理字符串到sql server。

But in general, ASp.NET will show dates, times, numbers in the local langauge of the browser. Either turn that off, or deal with it. It is nice for the user. So, check locales - user, server process. What is the thread current locale?

但一般来说,ASp.NET会在浏览器的本地语言中显示日期,时间和数字。要么关闭它,要么处理它。这对用户来说很好。因此,检查区域设置 - 用户,服务器进程。什么是线程当前区域设置?

#2

You need to use the SQL convert in stored procedure like:

您需要在存储过程中使用SQL转换,如:

convert(varchar, @yourdateParameter, 103) for format dd/mm/yyyy

convert(varchar, @yourdateParameter, 101) for format mm/dd/yyyy

Hope this helps.

希望这可以帮助。

#3

I've opened a new question about this once I'd narrowed the problem down to the RDLC report. It's answered and it's here.

一旦我将问题缩小到RDLC报告,我就已经提出了一个新问题。它已经回答了,就在这里。

#1

The text boxes are strings, which are in the thread local which is set from the browser (langauges).

文本框是字符串,它们位于从浏览器设置的本地线程中(langauges)。
Convert them to aa DateTime object first, using a local aware transformation, then write the dateTime object to the server. NEVER (!) deal with strings to sql server unless you format them in ISO independent form (2011-08-17 23:52:11).

首先使用本地感知转换将它们转换为DateTime对象,然后将dateTime对象写入服务器。除非你用ISO独立形式(2011-08-17 23:52:11)格式化它们,否则永远不会(!)处理字符串到sql server。

#2