Replacing default DateTime Value in SQL Server insert

Question

I am trying to insert data into a table in SQl Server, and I want the DateTime variables to be nullable, but whenever the user enters "", it is entered into the server as "1900-01-01 12:00:00". I tried to modify the insert statement to include a NULLIF, but when I do, I receive an incorrect syntax error, and I'm really stuck as to how to correct it. I've been to Microsoft's website and read the documentation, but I could not find an example of using a NULLIF on insert. The idea here is I want to compare the passed value to "", and if they are the same, return/insert NULL, if not insert the given value. I'm almost certain I have the right idea here, but I'm having trouble with the syntax of it. Any help would be GREATLY appreciated!

Please find the code below:

CREATE PROCEDURE [dbo].[spUser_Insert]
    @Company varchar(10), 
    @PartNum varchar(500), 
    @Plant varchar(10), 
    @ForeDate date, 
    @Inactive bit, 
    @ForeQty decimal(18,8), 
    @ForeQtyUOM varchar(10), 
    @ConsumedQty decimal(18,8), 
    @OrigForecastQty_c decimal(18,8), 
    @SumOrderQty decimal(18,8), 
    @NewForecastQty decimal (18,8), 
    @NewInactive bit, 
    @LastUpdatedDate datetime
AS
begin 
    insert into dbo.ExampleDb (Company, PartNum, Plant, ForeDate, Inactive, ForeQty, ForeQtyUOM, ConsumedQty, OrigForecastQty_c, SumOrderQty, NewForecastQty, NewInactive, LastUpdatedDate)
    values (@Company, @PartNum, @Plant, ForeDate = NULLIF(@ForeDate, ""), @Inactive, @ForeQty, @ForeQtyUOM, @ConsumedQty, @OrigForecastQty_c, @SumOrderQty, @NewForecastQty, @NewInactive, @LastUpdatedDate);
end

This is the table definition

CREATE TABLE [dbo].[ExampleDb]
(
    [Company] VARCHAR(10) NULL ,
    [PartNum] VARCHAR(500), 
    [Plant] VARCHAR(10) NULL, 
    [ForeDate] DATE NULL, 
    [Inactive] BIT NULL, 
    [ForeQty] DECIMAL(18, 8) NULL, 
    [ForeQtyUOM] VARCHAR(10) NULL, 
    [ConsumedQty] DECIMAL(18, 8) NULL, 
    [OrigForecastQty_c] DECIMAL(18, 8) NULL, 
    [SumOrderQty] DECIMAL(18, 8) NULL, 
    [NewForecastQty] DECIMAL(18, 8) NULL, 
    [NewInactive] BIT NULL, 
    [LastUpdatedDate] DATETIME NULL, 
    
)

Why is your "user" (which presumably means the application you wrote) providing a value for @LastUpdateDate? Usually this would be set within your stored procedure. But the real issue is how you are executing the stored procedure. If you pass NULL, then NULL will be inserted. If you attempt to pass an empty string, that unfortunately gets implicitly converted to the datetime you have discovered. So pass NULL, not an empty string. — SMor
– SMor, Commented Aug 10, 2022 at 15:03
And I just realized that you specifically state "datetime" in your description - of which there is only one parameter / column of that type. But you also specifically mention ForeDate which is date (not datetime). Please clear up the inconsistency - a DATE datatype will generally not be displayed by most applications with a time component. — SMor
– SMor, Commented Aug 10, 2022 at 15:07

Stu · Accepted Answer · 2022-08-10 15:08:29Z

2

Your values construct just needs the nullif statement with the correct empty string and no assignment/alias

values (..., NULLIF(@ForeDate, ''), ...);

answered Aug 10, 2022 at 15:08

Stu

32.7k6 gold badges17 silver badges34 bronze badges

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

Comments

RBarryYoung · Accepted Answer · 2022-08-10 15:14:58Z

0

Try it like this:

insert into dbo.ExampleDb (Company, PartNum, Plant, ForeDate, Inactive, ForeQty, ForeQtyUOM, ConsumedQty, OrigForecastQty_c, SumOrderQty, NewForecastQty, NewInactive, LastUpdatedDate)
select @Company, @PartNum, @Plant, NULLIF(@ForeDate, ""), @Inactive, @ForeQty, @ForeQtyUOM, @ConsumedQty, @OrigForecastQty_c, @SumOrderQty, @NewForecastQty, @NewInactive, @LastUpdatedDate;

This will fix the "Syntax Error" issue, but I'm not sure that it is going to fix your default date problem though, as it may be caused by a trigger or your table definition.

edited Aug 10, 2022 at 15:14

answered Aug 10, 2022 at 14:58

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

8 Comments

MuhammadFerr Over a year ago

When I try to implement your solution, I receive the following error "SQL80001: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name."

SMor Over a year ago

The double quote character is NOT a string delimiter.

RBarryYoung Over a year ago

@SMor Depends on the settings, but yep, you are probably right. Been a while for me, I will correct.

RBarryYoung Over a year ago

@MuhammadFerr OK, I will correct and doble-check it this time. One issue however is whether by "" you mean that @ForeDate is an empty string (probably) or do you mean that is is a string that actually contains quotation marks like '""'? (I am going to assume the former, unless you say otherwise).

MuhammadFerr Over a year ago

@RBarryYoung I'll update the question with the table definition, SRRY this was a goof up on my part for not including it in the first place!

|

Michael Bugglin · Accepted Answer · 2022-08-10 16:09:18Z

0

Your main problem is @ForeDate is a DATE, not a VARCHAR. Even if you fix the syntax error by using '' instead of "", dates can't contain empty strings as values, so your NULLIF(@ForeDate, ''), while it can potentially still work, is not proper date syntax, and can lead to unexpected future behavior.

Solution 1

Check the user's input in advance in your application, and, if it's an empty string, store NULL instead.

Solution 2

If you'd rather stick to a pure SQL solution, check for the minimum date instead of an empty string.

insert into dbo.ExampleDb (..., ForeDate, ...)
values (..., ForeDate = NULLIF(@ForeDate, '1900-01-01'), ...);

answered Aug 10, 2022 at 16:09

Michael Bugglin

1947 bronze badges

Comments

Jeremy Caney · Accepted Answer · 2022-08-15 00:55:26Z

0

Try with:

begin 
if  @ForeDate = ''
begin
set @ForeDate = null
end
    insert into dbo.ExampleDb (Company, PartNum, Plant, ForeDate, Inactive, ForeQty, ForeQtyUOM, ConsumedQty, OrigForecastQty_c, SumOrderQty, NewForecastQty, NewInactive, LastUpdatedDate)
    values (@Company, @PartNum, @Plant, @ForeDate, @Inactive, @ForeQty, @ForeQtyUOM, @ConsumedQty, @OrigForecastQty_c, @SumOrderQty, @NewForecastQty, @NewInactive, @LastUpdatedDate);
end

or

 insert into dbo.ExampleDb (Company, PartNum, Plant, ForeDate, Inactive, ForeQty, ForeQtyUOM, ConsumedQty, OrigForecastQty_c, SumOrderQty, NewForecastQty, NewInactive, LastUpdatedDate)
  values (@Company, @PartNum, @Plant, ForeDate = NULLIF(@ForeDate, '' no ""), @Inactive, @ForeQty, @ForeQtyUOM, @ConsumedQty, @OrigForecastQty_c, @SumOrderQty, @NewForecastQty, @NewInactive, @LastUpdatedDate);

edited Aug 15, 2022 at 0:55

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Aug 10, 2022 at 15:31

Erick Mejias Alvarez

12 bronze badges

1 Comment

Jeremy Caney Over a year ago

Could you edit your answer to a) clean up the indentation so your code is easier to read, and b) provide an explanation of what you’re doing here and why you believe it’s the best approach to solving the original problem? That will make this answer more useful for future readers.

Collectives™ on Stack Overflow

Replacing default DateTime Value in SQL Server insert

4 Answers 4

Comments

8 Comments

Solution 1

Solution 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

8 Comments

Solution 1

Solution 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related