SQL Server - 将默认DateTime值分配给存储过程中的参数

时间:2021-03-24 11:24:43

Maybe I'm being an idiot here, but I'm trying to assign a default DateTime value to a Stored Procedure in MS SQL Server 2005 but can't get it to work eg:

也许我在这里是个白痴,但我正在尝试将默认的DateTime值分配给MS SQL Server 2005中的存储过程,但无法让它工作,例如:

CREATE PROCEDURE MyProcedure
(
    @MyInteger    INT = 123,
    @MyDateTime   DATETIME = CONVERT(DATETIME, '1753-01-01 00:00:00', 20)
)
AS
BEGIN ... (snip)

So if the @MyDateTime input parameter isn't specified, it should use '1753-01-01 00:00:00' by default, but I get the error...

因此,如果未指定@MyDateTime输入参数,则默认情况下应使用'1753-01-01 00:00:00',但是我收到错误...

Incorrect syntax near the keyword 'CONVERT'.

I can get it to work for Integers just fine. What am I doing wrong?

我可以让它为Integers工作就好了。我究竟做错了什么?


Edit: from the answers below, I eventually went with the following inside the Sproc itself:

编辑:从下面的答案中,我最终在Sproc本身内部进行了以下操作:

CREATE PROCEDURE MyProcedure
(
    @MyInteger    INT = 123,
    @MyDateTime   DATETIME = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    SET @MyDateTime = COALESCE(@MyDateTime, CONVERT(DATETIME, '1753-01-01 00:00:00', 20))

    ...snip...
END

2 个解决方案

#1


7  

@MyDateTime DATETIME ='1753-01-01 00:00:00'

@MyDateTime DATETIME ='1753-01-01 00:00:00'

Or right within the sproc you can do this:

或者在sproc内你可以这样做:

SELECT @MyDateTime = (SELECT CONVERT(DATETIME, '1753-01-01 00:00:00', 20))

SELECT @MyDateTime =(SELECT CONVERT(DATETIME,'1753-01-01 00:00:00',20))

The error is due to calling CONVERT, which you cannot do when defaulting parameters.

该错误是由于调用CONVERT,在默认参数时您无法执行此操作。

#2


4  

You can't have the CONVERT() function call in there, just assign the default value using the relevent datetime string.

你不能在那里调用CONVERT()函数,只需使用相关的日期时间字符串分配默认值。

#1


7  

@MyDateTime DATETIME ='1753-01-01 00:00:00'

@MyDateTime DATETIME ='1753-01-01 00:00:00'

Or right within the sproc you can do this:

或者在sproc内你可以这样做:

SELECT @MyDateTime = (SELECT CONVERT(DATETIME, '1753-01-01 00:00:00', 20))

SELECT @MyDateTime =(SELECT CONVERT(DATETIME,'1753-01-01 00:00:00',20))

The error is due to calling CONVERT, which you cannot do when defaulting parameters.

该错误是由于调用CONVERT,在默认参数时您无法执行此操作。

#2


4  

You can't have the CONVERT() function call in there, just assign the default value using the relevent datetime string.

你不能在那里调用CONVERT()函数,只需使用相关的日期时间字符串分配默认值。