[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-09
7
Medium Priority
?
194 Views
Last Modified: 2014-09-05
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?
0
Comment
Question by:sxxgupta
  • 4
  • 2
6 Comments
 

Author Comment

by:sxxgupta
ID: 40251440
Thank you.  This whole website is all new to me.
0
 

Author Comment

by:sxxgupta
ID: 40251441
But still need a solution.....:)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40251450
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:sxxgupta
ID: 40251661
Yes I understand that, but cannot get the error to raise.
0
 

Author Comment

by:sxxgupta
ID: 40251678
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40251765
very good!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question