Complex Date Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complex Date Queries

Author

Topic

RichDef
Starting Member

2 Posts

Posted - 2003-03-13 : 12:22:53

Hello There!

I have a single table of News stories each with a DateAdded. I want to draw out the news stories based on the Date for example I want the data to be organized by the year.

Example output:
2000
Story1 1/1/2000
story2 2/1/2000
.
.
.
.
2001
Story56 1/1/2001
story57 2/1/2001
.
.
.
2002

How would I construct a query to create a result set like this?

Thanks.

-Rich

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-13 : 12:24:55

Have a look at the DATEPART function in SQL Server Books Online.

For example:
SELECT DATEPART(year, GETDATE())

You will get 2003 back as the year for the current date if your SQL Server has the correct date configured.

Tara

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-13 : 12:26:44

Well, you are going to want to use a SELECT. You can use predicate logic in your WHERE clause to chose the rows you want. You could, potentially, have a SELECT for each year and then UNION those together.

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-13 : 14:06:11

Please use a report writer or ASP page .

Select Year(Date), *
From
YourTable

is all SQL needs to do. The rest should be done by the client.

- Jeff

RichDef
Starting Member

2 Posts

Posted - 2003-03-13 : 16:14:50

I was hoping to avoid using individual select statements for each year! But, I may have no choice.

You see I wanted to create an all in one solution that would create the yearly break downs etc from the data in the table - rather than doing it on the client side. Using the Union suggestion I would have to get the range of dates - particularly the year ranges (I won't know how far back the stories go) and then generate the Select statements based on each year and then put them together. Its essentially the same thing you had suggested.

Either way it looks like I will have to do it the old fashioned way!!
Thank you all for your suggestions!

-Rich

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-13 : 16:31:17

OK,

How about:

CREATE TABLE Table1 (Col1 datetime, col2 varchar(25))
Go

INSERT INTO Table1 (Col1, Col2)
SELECT '01/01/2000','Article 1'
Union All
SELECT '02/01/2000','Article 2'
Union All
SELECT '03/01/2000','Article 3'
Union All
SELECT '01/01/2001','Article 4'
Union All
SELECT '02/01/2001','Article 5'
Union All
SELECT '03/01/2001','Article 6'
Union All
SELECT '01/01/2002','Article 7'
Go

SELECT IsNull(Convert(varchar(25),Col1), '')
, IsNull(Col2, '')
, CASE WHEN Col2 Is Null THEN ArticleYear Else Null End
FROM (
SELECT DISTINCT null As Col1, null As Col2, YEAR(Col1) As ArticleYear, 1 As RowGrouping
FROM TABLE1
Union All
SELECT Col1, Col2, YEAR(Col1) As ArticleYear, 2 As RowGrouping
FROM TABLE1) As XXX
ORDER By ArticleYear, RowGrouping, Col1

Drop Table Table1

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources