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

Dipesh Patel
Dipesh Patel used Ask the Experts™
on
check if the date "01-02-2020" is less then current date ie "21-11-2019", if so remove postponement date. But it removes date regardless of date being in the future.


IF ((@Postponement IS NOT NULL)AND(@Postponement <= Convert(Date, GetDate(), 101)))
      SET @set_postponement = NULL;

what am I doing wrong here?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

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

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.

Author

Commented:
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.
ste5anSenior Developer

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

Open in new window

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

crystal_ball_small.jpg
..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);

Open in new window

?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
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
ok I worked it out the statement below is causing it to be NULL running executing the query again when there is postponement date in the table.

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

school boy error
ste5anSenior Developer

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

Open in new window

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial