Inserting null when variable is empty for DATETIME

Question

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

>>>from my form<<< This means that from your form you pass not NULL, but smth else, I think you pass an empty string ('') that is then converted to 19000101 by server — sepupic
– sepupic, Commented Jan 25, 2018 at 10:41
Not clear how you're calling it but the problem sounds like you're passing an empty string as the parameter and letting SQL Server perform an implicit conversion to a datetime value. Instead of passing an empty string, pass the NULL literal instead. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 25, 2018 at 10:41
Empty and NULL aren't the same thing. If you pass an Empty String ('') to a datetime variable, it will default to '1900-01-01'. Are you passing an empty string, or a NULL? — Thom A
– Thom A ♦, Commented Jan 25, 2018 at 10:42

Thom A · Accepted Answer · 2018-01-25 10:46:02Z

1

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.

answered Jan 25, 2018 at 10:46

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thom A Over a year ago

@FahadAlt You're welcome. I'd consider having a rethink, however, on how you pass your data to your SQL Server. There's a very important distinction between NULL and '', and you need to ensure you use them correctly. As you've seen, passing '' to some data types will generate a different value to what you expected (for example, '' as a number is 0, and as a date is '1900-01-01'). Passing '' also means that you will not use the default value of a column or parameter. If you want to use a default value of a parameter, don't pass the parameter, and for a table, pass NULL.

Collectives™ on Stack Overflow

Inserting null when variable is empty for DATETIME

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related