Link to home
Start Free TrialLog in
Avatar of gianitoo
gianitoo

asked on

add 30 days before trigger date programatically?

this is my query
SELECT ID, Contractname, triggerdate, Amount, ClientID FROM legalcontract
triggerdate is the date where we need to follow up to client for possible renewal.   i want to add to my query a field called 30days  which is 30 days before the triggerdate.  

how can u add that programmaticaly in tsql?
Avatar of sphillips1971
sphillips1971
Flag of United States of America image

You can pass a negative to the DATEADD function to perform a date subtraction:

SELECT DATEADD(day, -30, triggerdate) AS 30days
FROM legalcontract

Scott
Avatar of gianitoo
gianitoo

ASKER

can my query look like this  
SELECT ID, DATEADD(day, -30, triggerdate) AS 30days, triggerdate,Contractname, startdate, enddate, Amount, ClientID FROM legalcontract
ASKER CERTIFIED SOLUTION
Avatar of sphillips1971
sphillips1971
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott Pletcher
I know the answer has already been accepted but you want still want to consider this approach :-) ...

I suggest using a computed, or virtual, column.  This will keep the column definition in the table itself and not in any particular SELECT.  First, you add the column definition once to the table:

ALTER TABLE legalcontract
ADD notifydate AS DATEADD(DAY, -30, triggerdate)

Then you can SELECT it just like any other column.  For example:

SELECT ID, Contractname, notifydate, Amount, ...
FROM legalcontract
WHERE notifydate ...
ORDER BY notifydate