此 SQL Server 存储过程采用多个参数,其中一个参数是datetime
数据类型。
我们刚刚开始在欧洲本地化应用程序,问题是他们收到了这个:
将数据类型 nvarchar 转换为日期时间时出错。
我已经阅读了datetime
,所以我相信正在发生的事情是应用程序将日期发布为 dd/mm/yyyy 到 SQL Server,但由于数据库的默认语言为英语并且需要 mm/dd/yyyy,因此存在错误。
产生错误的示例输入:
@LastUpdatedDate = N'21.01.2016 03:54:08'
我在欧洲的同事说,当他将数据库的默认语言设置为德语时,错误停止发生。
此错误尚未在美国/加拿大出现,我假设如果应用程序和SQL Server运行相同的默认datetime
格式,则没有问题。但是,我们(在美国(需要在欧洲使用他们的应用程序和数据库,所以现在我们得到了错误。
下面是存储过程:
CREATE PROCEDURE [dbo].[addUser]
(
@UserID NVARCHAR ( 50 ),
@AccountID NVARCHAR( 50 ) = '00000000000000000000000000000000',
@Password NVARCHAR( 50 ),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Telephone nvarchar(25),
@Mobile nvarchar(25),
@FAX nvarchar(25),
@EmailAddress nvarchar(50),
@CurrentUserID nvarchar(50),
@LastUpdatedBy nvarchar(50),
@LastUpdatedDate datetime
)
AS
BEGIN TRANSACTION
INSERT INTO tbUsers (UserID, AccountID, Password, Status, VPID, EvalStatusID, FirstName, LastName, Telephone, Mobile, FAX, EmailAddress, LastUpdatedBy, LastUpdatedDate)
VALUES (@UserID, @AccountID, @Password, 'A', 'PM', 'ACTIVE', @FirstName, @LastName, @Telephone, @Mobile, @FAX, @EmailAddress, @LastUpdatedBy, @LastUpdatedDate)
我还无法访问应用程序代码,但问题是:
有没有办法根据数据库的配置方式捕获错误并修复datetime
输入值?
我尝试在BEGIN TRANSACTION
之前使用SET DATEFORMAT dmy
,但在此之前它出错了。
是的,我们可能会更改应用程序代码以使用与语言无关的datetime
格式,但这可能不可行。
SQL Server 支持多种格式 - 请参阅 MSDN 联机丛书 CAST 和 CONVERT。这些格式中的大多数都取决于您拥有的设置 - 因此,这些设置有时可能有效 - 有时无效。
解决此问题的方法是使用 SQL Server 支持的(略微调整的(ISO-8601 日期格式 - 此格式始终有效 - 无论您的 SQL Server 语言和日期格式设置如何。
SQL Server 支持的 ISO-8601 格式有两种形式:
-
YYYYMMDD
只针对日期(没有时间部分(;请注意:没有破折号!,这非常重要!YYYY-MM-DD
并不独立于 SQL Server 中的日期格式设置,并且并非在所有情况下都有效!
或:
- 日期和时间
YYYY-MM-DDTHH:MM:SS
- 请注意:此格式有破折号(但可以省略(,以及固定T
作为DATETIME
日期和时间部分之间的分隔符。
这适用于 SQL Server 2000 及更高版本。
如果您使用的是 SQL Server 2008 或更高版本和 DATE
数据类型(仅DATE
- 不是 DATETIME
!(,那么您确实也可以使用 YYYY-MM-DD
格式,这也适用于 SQL Server 中的任何设置。
不要问我为什么整个话题如此棘手和有些混乱——就是这样。但是对于YYYYMMDD
格式,您应该适用于任何版本的 SQL Server 以及 SQL Server 中的任何语言和日期格式设置。
对于 SQL Server 2008 及更高版本,建议在只需要日期部分时使用DATE
,在需要日期和时间时使用DATETIME2(n)
。如果可能的话,您应该尝试开始逐步淘汰DATETIME
数据类型
更新:如果您使用的是SQL Server 2012或更高版本,则可以使用T-SQL中的新TRY_PARSE
函数,该函数允许您安全地分析表示日期的字符串。您可以指定要使用的区域设置,如果解析不起作用,则会返回NULL
而不是异常。
试试这个:
DECLARE @input NVARCHAR(100) = N'21.01.2016 03:54:08'
SELECT
TRY_PARSE(@input AS DATE USING 'en-gb')
应返回有效的 DATE
值 Jan-21, 2016 - 无论您的 SQL Server 设置为哪种语言/区域设置