Solved

add 30 days before trigger date programatically?

Posted on 2007-04-11
4
236 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:gianitoo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Expert Comment

by:sphillips1971
ID: 18892790
You can pass a negative to the DATEADD function to perform a date subtraction:

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

Scott
0
 
LVL 1

Author Comment

by:gianitoo
ID: 18892851
can my query look like this  
SELECT ID, DATEADD(day, -30, triggerdate) AS 30days, triggerdate,Contractname, startdate, enddate, Amount, ClientID FROM legalcontract
0
 
LVL 5

Accepted Solution

by:
sphillips1971 earned 500 total points
ID: 18892869
Yes, you can add that as a column anywhere in your SQL

Scott
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 18893112
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

623 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