Replacing default DateTime Value in SQL Server insert-CodePudding

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

CodePudding user response：

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

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

CodePudding user response：

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.