在将DateTime值传递给存储过程时保留格式 [英] Preserving format while passing DateTime values to Stored Procedure
问题描述
我在Web窗体上有一个TextBox服务器控件,例如 txtDueDate 。我不想使用DateTimePicker,但想使用TextBox本身。用户以dd / mm / yyyy格式输入日期。
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.
在将此值传递给SQL Server 2005(Express)存储过程时,我使用类似以下内容:
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
我想知道SQL Server 2005内部使用的默认格式来存储此日期。在我的PC上, Windows XP区域设置设置为dd / mm / yyyy格式。通过Management Studio查看记录时,记录已正确存储。但是我担心将使用此Web应用程序的用户。 Windows区域设置在他们的PC上可能会有所不同。
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.
我想确定无论是谁查询数据库,日期都以dd / mm / yyyy格式显示。如果有人在2004年2月1日插入,则它不应成为的有效反向日期,例如:2004年2月1日。
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.
推荐答案
DateTime
是 DateTime
是 DateTime
-在SQL Server中存储时,它没有任何(面向字符串的)格式(存储为64位长)。如果已经将参数作为 DateTime
传递给存储过程,那应该没问题!该值将由SQL Server 存储,而不会更改任何格式-因为它没有与之关联的任何格式...
A DateTime
is a DateTime
is a DateTime
- 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 a DateTime
already, 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...
当您在SQL Server Management Studio中查看日期或将日期转换为字符串格式(例如)时,仅是日期以给定的字符串格式表示您的.NET应用程序。
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.
当您需要以某种方式将字符串表示形式传递到SQL Server中(例如,用于搜索等)时,该功能最强大,并且会与任何区域/语言设置一起使用的是 ISO-8601 日期格式: YYYYMMDD
或(如果需要,时间部分) YYYY-MM-DDTHH:MM:SS
(其中中间的 T
是一个文字,分开日期和时间部分)
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: YYYYMMDD
or alternatively (if you need the time portion) YYYY-MM-DDTHH:MM:SS
(where the T
in the middle is a literal, separating the date and time portions)
这篇关于在将DateTime值传递给存储过程时保留格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!