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?
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?
ASKER
can my query look like this
SELECT ID, DATEADD(day, -30, triggerdate) AS 30days, triggerdate,Contractname, startdate, enddate, Amount, ClientID FROM legalcontract
SELECT ID, DATEADD(day, -30, triggerdate) AS 30days, triggerdate,Contractname, startdate, enddate, Amount, ClientID FROM legalcontract
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SELECT DATEADD(day, -30, triggerdate) AS 30days
FROM legalcontract
Scott