sql - 如何在TSQL中的表中输入日期？ (将数据类型 varchar 转换为 datetime 时出错)

Question

我想使用过程输入 30/10/1988 作为表中 DOB 列的日期

alter procedure addCustomer 
@userName varchar(50),
@userNIC varchar(50), 
@userPassword varchar(100), 
@userDOB datetime, 
@userTypeID int, 
@userEmail varchar(50), 
@userTelephone int, 
@userAddress char(100),
@userCityID int,
@status int output
as

    declare @userID int
    declare @eid int
    declare @tid int
    declare @aid int

    execute getLastRaw 'userID','tblUserParent', @userID output

    insert into tblUserParent values (@userID, @userName, @userNIC, @userPassword, @userDOB, @userTypeID)

    execute getLastRaw 'addressID','tblAddress', @aid output

    insert into tblAddress values (@aid, @userAddress, @userID, @userCityID)

    execute getLastRaw 'emailID','tblEmail', @eid output

    insert into tblEmail values (@eid, @userEmail, @userID)

    execute getLastRaw 'telephoneID','tblTelephoneNO', @tid output

    insert into tblTelephoneNO values (@tid, @userTelephone , @userID)
    insert into tblUserCustomer values (@userID, @eid , @tid, @aid)

...但是当我像这样输入“30/10/1988”时它会出错

消息 8114，级别 16，状态 5，过程 addCustomer，第 0 行将数据类型 varchar 转换为日期时间时出错。

...但是当我像 30/10/1988 一样进入时
'/' 附近的语法不正确

我该如何解决？

Answer 1

如果你真的想避免基于模糊日期的可能性，那么你应该总是在两个 unambiguous date formats 之一中输入它。已经选择了答案并且它是有效的，但我相信传播知识;)

正如@cloud 和我的帖子所注意到的那样，我的帖子代表了一个年轻的、不太聪明的我，只提供了一个链接的答案，我会弹出 archive 的内容Jamie Thompson 对 TSQL 中明确日期格式的回答

tl;博士;

yyyy-MM-ddTHH24:mi:ss

yyyyMMdd HH24:mi:ss

One of the most commonly used data types in SQL Server is [datetime] which unfortunately has some vagaries around how values get casted. A typical method for defining a [datetime] literal is to write it as a character string and then cast it appropriately. The cast syntax looks something like this: DECLARE @dt NVARCHAR(19) = '2009-12-08 18:00:00';

SELECT CAST(@dt AS datetime);

Unfortunately in SQL Server 2005 the result of the cast operation may be dependent on your current language setting. You can discover your current language setting by executing: SELECT @@LANGUAGE To demonstrate how your language setting can influence the results of a cast take a look at the following code: ALTER DATABASE tempdb

SET COMPATIBILITY_LEVEL = 90 ; --Behave like SQL Server 2005

USE tempdb

GO

DECLARE @t TABLE (

  dateString  NVARCHAR(19)

);

INSERT @t (dateString)

VALUES ('2009-12-08 18:00:00') --'yyyy-MM-dd hh24:mi:ss'

, ('2009-12-08T18:00:00') --'yyyy-MM-ddThh24:mi:ss'

, ('20091208 18:00:00') --'yyyyMMdd hh24:mi:ss'

SET LANGUAGE french;

SELECT 'french' AS lang

, DATENAME(MONTH,q.[dt]) AS mnth

, q.[dt]

FROM (

   SELECT  CAST(dateString AS DATETIME) AS dt

   FROM    @t

   )q;

SET LANGUAGE us_english;

SELECT 'us_english' AS lang

, DATENAME(MONTH,q.[dt]) AS mnth

, q.[dt]

FROM (

   SELECT  CAST(dateString AS DATETIME) AS dt

   FROM    @t

   )q; We are taking the value which can be described in words as “6pm on 8th December 2009”, defining it in three different ways, then

seeing how the @@LANGUAGE setting can affect the results. Here are those results: french language datetime Notice how the interpretation of the month can change depending on @@LANGUAGE. If @@LANGUAGE=’french’ then the string '2009-12-08 18:00:00' is interpreted as 12th August 2009 (‘août’ is French for August for those that don’t know) whereas if @@LANGUAGE=’us_english’ it is interpreted as 8th December 2009. Clearly this is a problem because the results of our queries have a dependency on a server-level or connection-level setting and that is NOT a good thing. Hence I recommend that you only define [datetime] literals in one of the two unambiguous date formats: yyyy-MM-ddTHH24:mi:ss yyyyMMdd HH24:mi:ss That was going to be the end of this blog post but then I found out that this behaviour changed slightly in SQL Server 2008. Take the following code (see if you can figure out what the results will be before I tell you): ALTER DATABASE tempdb

SET COMPATIBILITY_LEVEL = 100 ; --Behave like SQL Server 2008

GO

USE tempdb

GO

SET LANGUAGE french;

DECLARE @dt NCHAR(10) = '2009-12-08 18:00:00'; --Ambiguous date format

SELECT CAST(@dt AS datetime) AS [ExplicitCast]

, DATENAME(MONTH,@dt) AS [MonthFromImplicitCast]

, DATENAME(MONTH,CAST(@dt AS datetime)) AS [MonthFromExplicitCast]; Here we are doing three different things with our nchar literal: explicitly cast it as a [datetime] extract the month name from the char literal using the DATENAME function (which results in an under-the-covers implicit cast) extract the month name from the char literal using the DATENAME function after it has been explicitly casted as a [datetime] Note that the compatibility level is set to SQL Server 2008 and @@LANGUAGE=’french’. Here are the results: image (Were you correct?) Let’s take a look at what is happening here. The behaviour when we are explicitly casting as [datetime] hasn’t changed, our nchar literal is still getting interpreted as 12th August rather than 8th December when @@LANGUAGE=’french’. The [MonthFromExplicitCast] field is interesting though, it seems as though the implicit cast has resulted in the desired value of 8th December. Why is that? To get the answer we can turn to BOL’s description of the DATENAME function syntax: image The implicit cast is not casting to [datetime] at all, it is actually casting to [date] which is a new datatype in SQL Server 2008. The new date-related datatypes in SQL Server 2008 (i.e. [date], [datetime2], [time], [datetimeoffset]) disregard @@LANGUAGE and hence we get behaviour that is more predictable and, frankly, better. These new behaviours for SQL Server 2008 were unknown to me when I began this blog post so I have learnt something in the course of authoring it, I hope it has helped you too. No doubt someone somewhere is going to get nastily burnt by this at some point, make sure that it isn’t you by always using unambiguous date formats: yyyy-MM-ddTHH24:mi:ss yyyyMMdd HH24:mi:ss regardless of which version you are on!