Date sql query

Aleks
Aleks used Ask the Experts™
on
I have a table with news, one of the fields is 'date'.

I am using ASP/VBscript. and MS SQL 2008.

I need two queries.

First one to return all results which date is the cureent years.
The second query will group all news posted in previous years by year display the year and the number of news which have that year. So for example it will display:

-- This years news # 1 --
-- This years news # 2 --
-- etc

2011 (34)
2010 (21)
2009 (11)

Finally the years displayed will have a link to another page, i will pass on the year as parameter ... say :  yearid = 2009

So the last sql will display all news with the year passed, sort of like the first query only its not current year but year = request.querystring("yearid")

Hope that makes sense.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst
Commented:
"First one to return all results which date is the cureent years. "

SELECT * FROM whatever_table WHERE datepart(year, [date]) = datepart(year, getdate());


"The second query will group all news posted in previous years by year display the year and the number of news which have that year. So for example it will display:"


SELECT COUNT(*) as news_count, datepart(year, [date])  
FROM whatever_table WHERE datepart(year, [date]) = datepart(year, getdate())
GROUP BY datepart(year, [date])
Top Expert 2011
Commented:
Please try:
select *
from TheTable
where Year([date]) = Year(getdate())

select Year([date]) [Year], Count(1) [NewsCount]
from TheTable
where Year([date]) < Year(getdate())
group by Year([date])

select *
from TheTable
where Year([date]) = @yearid

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial