mahnamahna mahnamahna - 20 days ago 5
SQL Question

How to make a job step work

This code works when run as SQL Query in Microsoft SQL Server Management Studio 2008 but when is set as Job step then works only second condition (update when is not last day of month). What is wrong with this code?

--set next invoice date
declare @data nvarchar(10) --invoice date
set @data = CONVERT (date, GETDATE());

update table
set invoice_date = case when day(DATEADD(day,1,@data)) = 1 then --is last day of month
(SELECT convert(date,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))) -- set inovice date as last day of next month
else --is not last day of month
(select DATEADD(MM,1,@data)) --add one month to inovice date
end
where status = 'current' and invoice_date = @data -- only for current inovices

Answer

Try this. First condition will work on every month last day only.

--set next invoice date
DECLARE @data NVARCHAR(10) --invoice date
SET @data = CONVERT (DATE, GETDATE());

UPDATE table 
SET invoice_date = CASE WHEN DAY(DATEADD(DAY,1,@data)) = 1 
                   THEN --is last day of month
                      CAST( DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@data)+2,0)) AS DATE) -- set inovice date as last day of next month
                   ELSE --is not last day of month
                     (DATEADD(MM,1,@data)) --add one month to inovice date
                        END
WHERE status = 'current' AND invoice_date = @data  -- only for current inovices