将日期时间值传递给存储过程时保留格式
我在 Web 窗体上有一个 TextBox 服务器控件，例如 txtDueDate。我不想使用 DateTimePicker 但想使用 TextBox 本身。用户以 dd/mm/yyyy 格式输入日期。
将此值传递给 SQL Server 2005 (Express) 存储过程时，我使用类似以下内容：
cmdParameters.AddWithValue("@DueDate", Convert.ToDateTime(txtDueDate.Text));
存储过程具有输入参数来处理此值，并声明为：
@DueDate SmallDateTime
我想知道 SQL Server 2005 在内部使用的默认格式来存储此日期。在我的电脑上，Windows XP 区域设置设置为 dd/mm/yyyy 格式。通过 Management Studio 查看记录时，记录可以正确存储。但我担心将使用这个网络应用程序的用户。 PC 上的 Windows 区域设置可能有所不同。
我想确保无论谁查询数据库，日期都以 dd/mm/yyyy 格式显示。如果有人插入 02/01/2004，那么它不应成为有效的反向日期，例如：01/02/2004。
I have a TextBox server control on a Web Form, say, txtDueDate. I don't want to use DateTimePicker but want to use TextBox itself. The user enters date in dd/mm/yyyy format.
While passing this value to SQL Server 2005 (Express) stored procedure, I use something like:
cmdParameters.AddWithValue("@DueDate", Convert.ToDateTime(txtDueDate.Text));
The stored procedure has input parameter to handle this value and is declared as:
@DueDate SmallDateTime
I want to know the default format SQL Server 2005 uses internally to store this date. On my PC, the Windows XP Regional Settings are set to dd/mm/yyyy format. While viewing records through Management Studio, it is stored correctly. But I am worried about users who will be using this web application. The Windows Regional Settings may differ on their PC.
I want to be sure whoever queries the database, the date is displayed in dd/mm/yyyy format. If someone inserts, 02/01/2004, then it should not become a valid reverse date like: 01/02/2004.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
DateTime是DateTime是DateTime- 当存储在 SQL Server 中时，它不“具有”任何（面向字符串的）格式（它存储为 64 位长）。如果您已经将参数作为DateTime传递给存储过程，那么应该没问题！该值将由 SQL Server 存储，不更改任何格式 - 因为它没有任何与之关联的格式...日期表示的唯一一点给定的字符串格式是当您在 SQL Server Management Studio 中查看它时，或者当您在 .NET 应用程序中将其转换为字符串格式时。
当您需要以某种方式将字符串表示形式传递到 SQL Server 中（例如用于搜索等）时，最强大且适用于任何区域/语言设置的就是ISO -8601 日期格式：
YYYYMMDD或者（如果您需要时间部分）YYYY-MM-DDTHH:MM:SS（其中T中间是一个文字，分隔日期和时间部分）A
DateTimeis aDateTimeis aDateTime- it doesn't "have" any (string-oriented) format when stored in SQL Server (it's stored as a 64-bit long). If you pass in a parameter to a stored procedure as aDateTimealready, you should be just fine! The value will be stored by SQL Server without changing any formatting - since it doesn't have any formatting associated with it...The only point the date is represented in a given string format is when you look at it in SQL Server Management Studio, or when you convert it to a string format in e.g. your .NET app.
When you need to somehow pass in a string-representation into SQL Server (e.g. for searching etc.), the one that's the most robust and will work with any regional/language setting is the ISO-8601 date format:
YYYYMMDDor alternatively (if you need the time portion)YYYY-MM-DDTHH:MM:SS(where theTin the middle is a literal, separating the date and time portions)无论如何，
DateTime值不应该以基于文本的格式存储在数据库中 - 听起来似乎并非如此。您应该将它们视为DateTime值。这些值在输出时的格式取决于查询数据库的内容，如果不基本上只存储文本，您就无法控制它，而您不应该这样做。 尽可能多地以“本机”格式处理值。尽可能避免与字符串格式之间的转换。我还会避免在这里仅使用
Convert.ToDateTime(text)- 您有多确定系统默认格式是此处使用的正确格式？您可以使用一组控件来为您提供实际值，而不是依赖于用户键入的内容吗？DateTimevalues shouldn't be stored as a text-based format in the database anyway - and it sounds like they're not. You should just treat them asDateTimevalues. How those values are formatted on the way out is up to whatever's querying the database, and you can't control that without basically just storing text, which you should not do. Treat the value in its "native" format for as much of the time as possible. Avoid conversions to and from string formats as far as you can.I would also avoid just using
Convert.ToDateTime(text)here - how certain are you that the system default format is the correct one to use here? Can you use a set of controls which give you the actual value instead of relying on what the user happens to type?您的选择...
1) 如果您担心用户输入错误的格式（错误如 DateTime.Parse 引发异常），那么您需要验证输入
2) 如果您担心用户输入错误的格式（错误如 DateTime.Parse 引发异常），然后使用日期选择器。
You choices...
1) IF you're worried about users entering a wrong format (wrong as in DateTime.Parse throws an exception), then you need to validate the input
2) IF you're worried about users entering a wrong format (wrong as in DateTime.Parse throws an exception), then use a date picker.
到日期的转换是在客户端完成的，方法是：
默认情况下，这使用当前的 UI 区域性，但您可以使用第二个参数指定区域性：
The conversion to date is done client side, by:
By default, this uses the current UI culture, but you can specify a culture with the second parameter: