CODEWITHSUNDEEP
sqlsql-serverdatabase
MuhammadFerr
MuhammadFerr

Reputation: 11

Replacing default DateTime Value in SQL Server insert

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, 
    
)

Upvotes: 0

Views: 85

Answers (4)

Erick Mejias Alvarez
Erick Mejias Alvarez

Reputation: 1

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);

Upvotes: 0

Michael Bugglin
Michael Bugglin

Reputation: 194

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'), ...);

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56725

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.

Upvotes: 0

Stu
Stu

Reputation: 32589

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

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

Upvotes: 2

Related Questions