sql script to raise error when the difference between two dates exceeds 30

I want to be able to raise error 50002 when [MDRDate]-[AwareDate]>30.  How can I do this?  This is the update trigger script below which raises errors when the dates are > than today's date for different date fields.

USE [crf]
GO
/****** Object:  Trigger [dbo].[InvalidDateTrigger_UPDATE]    Script Date: 08/09/2014 22:46:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[InvalidDateTrigger_UPDATE] 
      ON [dbo].[MDRData]
    AFTER UPDATE
AS 
SET NOCOUNT ON

DECLARE @future_date datetime, @days int

-- set date limit to tomorrow at midnight
SET @future_date = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)


--SELECT DATEDIFF(d, [AwareDate], [MDRDate]) as days
--FROM MDRData

--exec sp_addmessage @msgnum = 50002,@severity =11,@msgtext = 'This MDR is late!',@lang = 'us_english'
--exec sp_dropmessage @msgnum = 50002
--select * from sys.messages


UPDATE mdr
-- undo EventDate change if the new value is invalid
SET EventDate = d.EventDate
FROM [dbo].[MDRData] mdr
INNER JOIN deleted d ON
    d.MDRNum = mdr.MDRNum
WHERE
    --any time today is OK, make sure date is less than tomorrow or later
    mdr.EventDate >= @future_date

UPDATE mdr
-- undo EquipReturnDate change if the new value is invalid
SET EquipReturnDate = d.EquipReturnDate
FROM [dbo].[MDRData] mdr
INNER JOIN deleted d ON
    d.MDRNum = mdr.MDRNum
WHERE
    --any time today is OK, make sure date is less than tomorrow or later
    mdr.EquipReturnDate >= @future_date
    
UPDATE mdr
-- undo AwareDate change if the new value is invalid
SET AwareDate = d.AwareDate
FROM [dbo].[MDRData] mdr
INNER JOIN deleted d ON
    d.MDRNum = mdr.MDRNum
WHERE
    --any time today is OK, make sure date is less than tomorrow or later
    mdr.AwareDate >= @future_date
    
UPDATE mdr
-- undo MDRDate change if the new value is invalid
SET MDRDate = d.MDRDate
FROM [dbo].[MDRData] mdr
INNER JOIN deleted d ON
    d.MDRNum = mdr.MDRNum
WHERE
    --any time today is OK, make sure date is less than tomorrow or later
    mdr.MDRDate >= @future_date


-- check if any errors were found so message can be issued

IF EXISTS(SELECT 1 FROM inserted WHERE EventDate >= @future_date)
BEGIN
    --ROLLBACK TRANSACTION --will undo *ALL* of the UPDATE, every column and row
    RAISERROR (50001,11,1) WITH NOWAIT
END
IF EXISTS(SELECT 1 FROM inserted WHERE EquipReturnDate >= @future_date)
BEGIN
    --ROLLBACK TRANSACTION --will undo *ALL* of the UPDATE, every column and row
    RAISERROR (50001,11,1) WITH NOWAIT
END
IF EXISTS(SELECT 1 FROM inserted WHERE MDRDate >= @future_date)
BEGIN
    --ROLLBACK TRANSACTION --will undo *ALL* of the UPDATE, every column and row
    RAISERROR (50001,11,1) WITH NOWAIT
END
IF EXISTS(SELECT 1 FROM inserted WHERE AwareDate >= @future_date)
BEGIN
    --ROLLBACK TRANSACTION --will undo *ALL* of the UPDATE, every column and row
    RAISERROR (50001,11,1) WITH NOWAIT
END

Open in new window

I want to be able to raise error 50002 when [MDRDate]-[AwareDate]>30.  How can I do this?
sxxguptaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sxxguptaAuthor Commented:
Thank you.  This whole website is all new to me.
0
sxxguptaAuthor Commented:
But still need a solution.....:)
0
PortletPaulfreelancerCommented:
you would use datediff() to make the calculation

datediff(day,[MDRDate],[AwareDate]) >30

there are several examples of raising an error in the trigger already

if this isn't the information you need please let us know
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sxxguptaAuthor Commented:
Yes I understand that, but cannot get the error to raise.
0
sxxguptaAuthor Commented:
Did this to solve:

UPDATE mdr
-- undo MDRDate change if the new value is invalid
SET MDRDate = d.MDRDate
FROM [dbo].[MDRData] mdr
INNER JOIN deleted d ON
    d.MDRNum = mdr.MDRNum
WHERE
    DATEDIFF(day, d.[MDRDate], d.[AwareDate]) >30


-- check if any errors were found so message can be issued

IF EXISTS (SELECT 1 FROM inserted WHERE DATEDIFF(d, [AwareDate], [MDRDate]) > 30)
BEGIN
      RAISERROR (50002,11,1) WITH NOWAIT
END
0
PortletPaulfreelancerCommented:
very good!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.