CODEWITHSUNDEEP
sqldatedateadd
kumar chaudhari
kumar chaudhari

Reputation: 89

subtract date from sql column

need ur help again

i have a sql table with column docid,proposedcompletiondate,docstatus

doc_id     ProposedCompdate    Docstatus
0001         2011-10-30           A
0002         2011-11-29           C
0003         2011-10-31           C
0004         2011-10-30           A
0005         2011-10-30           C
0006         2011-10-28           C
0007         2011-10-25           A
0008         2011-10-30           C

Now i want to write a query to fetch results with Docstatus 'A' whose date when subtracted by 9 days should be current date

for eg if todays Date is '21-10-2011'

the resultset must be 

doc_id     ProposedCompdate    Docstatus
0001         2011-10-30           A
0004         2011-10-30           A

Upvotes: 1

Views: 6811

Answers (4)

Jithu
Jithu

Reputation: 511

Please use DATEADD SQL SERVER function where you can add or subtract the date.

for example,

SELECT DATEADD(day,-9, '2006-06-01') as date_diff;

According to your question, query may look like,

SELECT DATEADD(day,-9, 
   (SELECT ProposedCompdate  FROM Table_name where Docstatus = 'A')) 
AS "-9 Days";

Please read more about DATEADD function here !!

Upvotes: 1

Vamsi
Vamsi

Reputation: 4253

Works fine sql server 2008

CREATE TABLE #Docs (doc_id varchar(10), ProposedCompdate date,Docstatus  nvarchar(50))

INSERT INTO #Docs(doc_id, ProposedCompdate, Docstatus) VALUES(0001,'2011-10-30','A')
INSERT INTO #Docs(doc_id, ProposedCompdate, Docstatus) VALUES(0002,'2011-11-29','C')
INSERT INTO #Docs(doc_id, ProposedCompdate, Docstatus) VALUES(0003,'2011-10-31','C')
INSERT INTO #Docs(doc_id, ProposedCompdate, Docstatus) VALUES(0004,'2011-10-30','A')
INSERT INTO #Docs(doc_id, ProposedCompdate, Docstatus) VALUES(0005,'2011-10-30','C')
INSERT INTO #Docs(doc_id, ProposedCompdate, Docstatus) VALUES(0006,'2011-10-28','C')
INSERT INTO #Docs(doc_id, ProposedCompdate, Docstatus) VALUES(0007,'2011-10-25','A')
INSERT INTO #Docs(doc_id, ProposedCompdate, Docstatus) VALUES(0008,'2011-10-30','C')

SELECT * FROM #Docs WHERE CONVERT(DATE, DATEADD(D, 9, GETDATE())) = ProposedCompdate AND Docstatus = 'A'

DROP TABLE #Docs

Upvotes: 0

Tommy
Tommy

Reputation: 39807

This is assuming Microsoft SQL Server, but your where clause would be:

where Docstatus = 'A' AND DateDiff(day, GETDATE(), ProposedComplete) = 9

The DateDiff function takes a measurement parameter (day, year, etc), a beginning date and and end date and returns an integer. So the difference between today and ProposedComplete should be 9 days.

Upvotes: 1

Jinesh Jain
Jinesh Jain

Reputation: 1242

select doc_id,ProposedCompdate,Docstatus from tableName where convert(varchar(20),ProposedCompdate,101) = convert(varchar(20),dateadd(d,9,getdate()),101) 
and Docstatus='A'

Upvotes: 0

Related Questions