我有一个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 = @DataIDI 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.
更多推荐
发布评论