CODEWITHSUNDEEP
sql-server
sk7730
sk7730

Reputation: 736

SQL Select Query for 7 year old data

I have Project table.

    Proj_ID Enter_Date
    1       2001-10-17 00:00:00.000
    2       2012-10-17 00:00:00.000
    3       2013-10-17 00:00:00.000
    4       2013-10-18 00:00:00.000
    5       2005-10-18 00:00:00.000
    6       2006-10-18 00:00:00.000
    7       2013-10-18 00:00:00.000
    8       2002-10-18 00:00:00.000
    9       2003-10-18 00:00:00.000

I am trying to select all proj_id older than 7 years by comma separated into a declared variable. Can anyone help how can i do that?

For Eg: Expecting result as 1,5,6,8,9

Upvotes: 0

Views: 1010

Answers (5)

Kiril Rusev
Kiril Rusev

Reputation: 753

Try the following:

 DECLARE @result varchar(100)
 SET @result =
(SELECT distinct (proj_id+ ' ')
FROM table_name
WHERE DATEDIFF(year,Enter_date, GETDATE())>=7 
ORDER BY (proj_id + ' ')
FOR XML PATH (''))
SELECT REPLACE(RTRIM(@result),' ',',')

Upvotes: 1

siva-user3060012
siva-user3060012

Reputation: 46

try this following query

DECLARE @result NVARCHAR(MAX)
SET @result= Stuff((SELECT distinct ', ' + CAST(p.Proj_ID as NVARCHAR(10)) FROM tblProject p 
WHERE year(p.Enter_Date) <= (year(GetDate()) -7)  
FOR XML PATH('')),1,1,'')
SELECT @result

Upvotes: 1

nrsharma
nrsharma

Reputation: 2572

For getting the comma separated list you have to use STUFF.

Try this

SELECT STUFF ((SELECT ',' + Proj_ID FROM your_table 
WHERE YEAR(Enter_Date) >= (YEAR(GETDATE()-7)) FOR XML PATH('')), 1, 1, '') 
AS Enter_Date

And if you get the conversion fail error then cast your Id like this

SELECT STUFF ((SELECT ',' + CAST(Proj_ID AS VARCHAR) FROM your_table 
WHERE YEAR(Enter_Date) >= (YEAR(GETDATE()-7)) FOR XML PATH('')), 1, 1, '') 
AS Enter_Date

Upvotes: 1

Mike
Mike

Reputation: 2559

SELECT Proj_ID = STUFF((SELECT ', ' + Proj_ID FROM yourtable 
      WHERE Year(Enter_DATE) >= Year(GETDATE()) - 7) FOR XML PATH('')), 1, 2, '')

will put the reuslts into a comma seperated list

Upvotes: 0

Cyrus
Cyrus

Reputation: 131

I am not sure if this work in sql-server, I can't try now:

select Proj_ID
from your_table
where YEAR(Enter_Date) >= (YEAR(GetDate()) -7)

Upvotes: 0

Related Questions