Check if date is future or past, if past remove date how to do this?

You're not working correctly with data types. Maybe because you mix visualization (formatting) of data with the actual data.

The snippet should read:

-- What is @set_postponement?
-- Why setting it to NULL later?
DECLARE @set_postponement BIT = 1;

-- The data type @Postponement must be DATE.
-- Either as separate declaration or as function/procedure parameter.
DECLARE @Postponement DATE = GETDATE() - 1;

IF (   @Postponement IS NOT NULL
       AND @Postponement <= GETDATE())
BEGIN
    SET @set_postponement = NULL;
END;

SELECT @Postponement ,
       @set_postponement;

Select allOpen 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.

DECLARE @Postponement datetime;
DECLARE @current_Date datetime;
DECLARE @cur_postponement datetime;


IF (@Postponement IS NOT NULL
       AND @Postponement <= GETDATE())
      SET @set_postponement = NULL;

UPDATE [jargonf1_sqladmin].[table] SET [Postponement Date] = @set_postponement
where jargonf1_sqladmin.table.[Employee Number] = @EmployeeId;

so remove the current postponement in table if its has passed, if the date is still in the future don't remove it, this is what I am trying to do. Again still NULL's out the date even if its in the future.

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..

Well, why do you expect something different? When I correct your sample, so that it runs, it must NULL it..

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

Select allOpen in new window

When I need to guess, and I have to try really hard with your given samples and context..


..do you mean something like this:

UPDATE jargonf1_sqladmin.[table]
SET    [Postponement Date] = NULL
WHERE  jargonf1_sqladmin.[table].[Employee Number] = @EmployeeId
       AND [Postponement Date] < CAST(GETDATE() AS DATE);

Select allOpen in new window

?

full working code:




DECLARE @Postponement date;
DECLARE @current_Date date;
DECLARE @cur_postponement date;
DECLARE @set_postponement date;
DECLARE @cur_postponement2 date;
SET @current_Date = CONVERT(datetimeoffset, GETDATE());

DECLARE @cur_MONTH int;
DECLARE @cur_YEAR int;
SET @cur_MONTH = MONTH(@current_Date);
SET @cur_YEAR = DATEPART (year, @current_Date);

SET @cur_postponement2 = DATEFROMPARTS(@cur_YEAR, @cur_MONTH, 1);
SET @cur_postponement = DATEADD(month, 3, @cur_postponement2);


DECLARE @EmployeeId nvarchar(50)


DECLARE AE_Cursor CURSOR FOR
    SELECT
      jargonf1_sqladmin.AE_Engine.[Employee Number],
      jargonf1_sqladmin.AE_Engine.[Postponement Date]
      FROM jargonf1_sqladmin.AE_Engine;

OPEN AE_Cursor

FETCH NEXT FROM AE_Cursor INTO
    @EmployeeId, @Postponement

WHILE @@FETCH_STATUS = 0
BEGIN

IF (   @Postponement IS NOT NULL
       AND @Postponement <= CAST(GETDATE() AS DATE))
BEGIN
    SET @set_postponement = NULL;
END;


IF (@Postponement IS NULL)
      SET @set_postponement = @cur_postponement;
 


UPDATE [jargonf1_sqladmin].[AE_Engine] SET [Postponement Date] = @set_postponement
where jargonf1_sqladmin.AE_Engine.[Employee Number] = @EmployeeId;


FETCH NEXT FROM AE_Cursor INTO
    @EmployeeId, @Postponement

END


CLOSE AE_Cursor
DEALLOCATE AE_Cursor

Well, why that cursor? It's not necessary at all..

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

Select allOpen in new window

using the curser, as I want it to go through each record in the table