Selecting Year from Date (SQL)

Benzino

Hey, SQL noob here

I have a table for news items that stores the id, headline, description and the the time and date (datetime) it was created.

What I want to do is to make a kind of archive system, where it shows each year where there was a news item posted, and then the months in that year in which news was posted. Kinda like this:

> 2009
> Feb
> April
> 2010

Anyway, the layout isn't important. What im wondering is if there is anyway using SQL to get a list of the unique years and months in which news was added to the database.

Say I have the following data in the table:

id headline description created_at
1 Headline1 Description1 2009-10-22 : 23:34:12
2 Headline2 Description2 2009-12-24 : 18:28:39
3 Headline3 Description3 2010-01-06 : 20:07:48

I want to query the database to return the unique years in which news was posted, so in this case it would return id 1 & 2 (or the actual year itself, 2009 and 2010). Then I would send another query looking for the months in that year in which news was posted.

I know I could do just get the list of all the entries, loop through them using php and get the years and months myself, however I think that would be quite time consuming in terms of getting all the data, looping through them and checking each one.

Is there anyway of doing this with SQL? (excuse me if this is a totally noobish question )

P.S. the created_at data is of type datetime, however I can change that if necessary.

none

What Database product you use?
For T-SQL (Sybase, Microsoft) it's

DATEPART (YEAR, DATE)

Microsoft also has YEAR function.
For PL/SQL, you would use

EXTRACT(YEAR FROM DATE)

or

TO_CHAR(SYSDATE, 'YYYY')

Jagera

If its Microsoft, you can create a calculated column on the table:

alter table YourTable
add created_at_year as datepart(yy,created_at)

alter table YourTable
add created_at_month as datepart(mm,created_at)

Benzino

none wrote: »

What Database product you use?
For T-SQL (Sybase, Microsoft) it's

DATEPART (YEAR, DATE)

Microsoft also has YEAR function.
For PL/SQL, you would use

EXTRACT(YEAR FROM DATE)

or

TO_CHAR(SYSDATE, 'YYYY')

To be honest, I have no idea what database product im using I'm just connecting to a database made in phpMyAdmin through php.

I tried the Year function, and it worked! I also see they have a Month function too which will prove to be handy.

One more question tho, is there any way to get it to only return a year once. For example, I have 3 entries in the year 2009, is it possible for set the query so that it will only return 2009 once, rather than 3 times?

Thanks for all your help, I greatly appreciate it

NeverSayDie

If it's PHPMyAdmin, it's probably MySQL you're using.

Re getting unique years only, the DISTINCT keyword should do the trick, see here;
http://www.sql-tutorial.com/sql-distinct-sql-tutorial/

Benzino

NeverSayDie wrote: »

If it's PHPMyAdmin, it's probably MySQL you're using.

Re getting unique years only, the DISTINCT keyword should do the trick, see here;
http://www.sql-tutorial.com/sql-distinct-sql-tutorial/

Yes your right, and that worked.

Thanks very much all, I really appreciate it!