Again still NULL's out the date even if its in the future.Please post a concise and complete example. This means runnable, error-free T-SQL. Especially as you use reserved words as object names..
-- Why are the declared as DATETIME?
DECLARE @Postponement DATETIME;
DECLARE @current_Date DATETIME;
DECLARE @cur_postponement DATETIME;
DECLARE @set_postponement BIT;
-- Where does @Postponement gets a value from?
-- Where is @set_postponement declared?
-- Where does @set_postponement get a default value?
IF ( @Postponement IS NOT NULL
AND @Postponement <= GETDATE())
SET @set_postponement = NULL;
-- Well, that was to expect.
SELECT @Postponement,@set_postponement;
UPDATE [jargonf1_sqladmin].[table]
SET [Postponement Date] = @set_postponement
WHERE jargonf1_sqladmin.[table].[Employee Number] = @EmployeeId;
When I need to guess, and I have to try really hard with your given samples and context..UPDATE jargonf1_sqladmin.[table]
SET [Postponement Date] = NULL
WHERE jargonf1_sqladmin.[table].[Employee Number] = @EmployeeId
AND [Postponement Date] < CAST(GETDATE() AS DATE);
?
DECLARE @current_Date DATE = CONVERT(datetimeoffset, GETDATE());
DECLARE @cur_postponement DATE = DATEADD(month, 3, DATEFROMPARTS(YEAR(@current_Date), MONTH(@current_Date), 1));
UPDATE [jargonf1_sqladmin].[AE_Engine]
SET [Postponement Date] = CASE WHEN [Postponement Date] IS NULL THEN @cur_postponement
WHEN [Postponement Date] <= GETDATE() THEN @cur_postponement
ELSE [Postponement Date]
END
WHERE CASE WHEN [Postponement Date] IS NULL THEN @cur_postponement
WHEN [Postponement Date] <= GETDATE() THEN @cur_postponement
ELSE [Postponement Date]
END != [Postponement Date];
The snippet should read:
Open in new window
Without the actual code, I need to guess:You're running into an implicit cast from DATE to VARCHAR with a different locale (server or session setting) and due to the lexical sort of the VARCHAR it does not work.