在DATETIME变量为空时插入空值(Inserting null when variable is empty for DATETIME)

我有一个sp从我的表单更新我的表。当我尝试sp运行更新并将datetime字段留空时，它将默认日期时间设置为“1900-01-01 00：00：00.000”我怎样才能将它设置为空？

我已经检查了我的表单发送填充日期的值是空的。

我在这里有sp的例子：

ALTER PROCEDURE [dbo].[DBK_spDataUpdate] @FirstName AS NVARCHAR(250)= NULL @FillDate AS DATETIME = NULL @DataID AS BIGINT SET NOCOUNT ON; UPDATE dbo.DBK_tbData SET FirstName = @FirstName, FillDate = @FillDate WHERE DataID = @DataID

I have an sp that update my table from my form. When I try the sp is running to update and leave the datetime field empty, it put the default datetime as "1900-01-01 00:00:00.000" How can I set it to be null?

I have checked the values my form send the filldate is empty.

I have example of sp here:

ALTER PROCEDURE [dbo].[DBK_spDataUpdate] @FirstName AS NVARCHAR(250)= NULL @FillDate AS DATETIME = NULL @DataID AS BIGINT SET NOCOUNT ON; UPDATE dbo.DBK_tbData SET FirstName = @FirstName, FillDate = @FillDate WHERE DataID = @DataID

最满意答案

在这里使用一个小水晶球，但我相信OP会将一个空字符串（ '' ）传递给datetime参数，而不是NULL 。这是两个非常不同的价值观; 因为''将导致日期时间0 （即1900-01-01' ）。

如果这个猜测是正确的，那么你可以使用NULLIF ：

ALTER PROCEDURE [dbo].[DBK_spDataUpdate] @FirstName AS NVARCHAR(250)= NULL, @FillDate AS DATETIME = NULL, @DataID AS BIGINT AS SET NOCOUNT ON; UPDATE dbo.DBK_tbData SET FirstName = @FirstName, FillDate = NULLIF(@FillDate, '19000101') WHERE DataID = @DataID;

否则，而不是传递一个空字符串，传递NULL 。

using a small crystal ball here, but I believe the OP is passed an empty string ('') to the datetime parameter, not a NULL. These are two very different values; as '' will result in the datetime 0 (which is 1900-01-01').

If this guess is correct, then you could use NULLIF:

ALTER PROCEDURE [dbo].[DBK_spDataUpdate] @FirstName AS NVARCHAR(250)= NULL, @FillDate AS DATETIME = NULL, @DataID AS BIGINT AS SET NOCOUNT ON; UPDATE dbo.DBK_tbData SET FirstName = @FirstName, FillDate = NULLIF(@FillDate, '19000101') WHERE DataID = @DataID;

Otherwise, instead of passing an empty string, pass a NULL.

更多推荐