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.
Check if date is future or past, if past remove date how to do this?
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?
IF ((@Postponement IS NOT NULL)AND(@Postponement <= Convert(Date, GetDate(), 101)))
SET @set_postponement = NULL;
what am I doing wrong here?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You're not working correctly with data types. Maybe because you mix visualization (formatting) of data with the actual data.
The snippet should read:
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.
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;
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
where jargonf1_sqladmin.table.[E
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.
DECLARE @current_Date datetime;
DECLARE @cur_postponement datetime;
IF (@Postponement IS NOT NULL
AND @Postponement <= GETDATE())
SET @set_postponement = NULL;
UPDATE [jargonf1_sqladmin].[table
where jargonf1_sqladmin.table.[E
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;
..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);
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_Engin
jargonf1_sqladmin.AE_Engin
FROM jargonf1_sqladmin.AE_Engin
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_En
where jargonf1_sqladmin.AE_Engin
FETCH NEXT FROM AE_Cursor INTO
@EmployeeId, @Postponement
END
CLOSE AE_Cursor
DEALLOCATE AE_Cursor
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_Engin
jargonf1_sqladmin.AE_Engin
FROM jargonf1_sqladmin.AE_Engin
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_En
where jargonf1_sqladmin.AE_Engin
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_En
where jargonf1_sqladmin.AE_Engin
school boy error
UPDATE [jargonf1_sqladmin].[AE_En
where jargonf1_sqladmin.AE_Engin
school boy error
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];
Premium Content
You need an Expert Office subscription to comment.Start Free Trial