SQL for finding years in dates?

[Jason Tudisco]

Hello, I not sure if this is the right place to ask this... I am using
mysql.

What I need is a SQL statement that can find what years are in the
database that is greater than last year.

For example...

I have a table full of dates ranging from 2001 to 2005..

I am straining my brain to figure out a SQL statement that can give me
a list of years greater than last year..

In this case I need a list that has..

2003
2004
2005

If anyone could help me.. or give me a clue.. I would appreciate it.

[Jason Tudisco]

I finally did figure out how to do this.. I post my solution just in
case somebody else finds it useful or has a suggestion for improvment.

SELECT YEAR(startdate) AS year
FROM course_dates
WHERE YEAR(startdate) > (YEAR(NOW())-1)
GROUP BY YEAR(startdate)

Aggro <sp**********@y ahoo.com> wrote in message news:<AC******* *****@read3.ine t.fi>...

Jason Tudisco wrote:

Hello, I not sure if this is the right place to ask this... I am using
mysql.

What I need is a SQL statement that can find what years are in the
database that is greater than last year.

For example...

I have a table full of dates ranging from 2001 to 2005..

I am straining my brain to figure out a SQL statement that can give me
a list of years greater than last year..

In this case I need a list that has..

2003
2004
2005

If anyone could help me.. or give me a clue.. I would appreciate it.

Use mysql function year(date) to extract year from date, and compare
years. For example

--------------------------------------------------
mysql> create table datetest(date_ date);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into datetest values( '2002-01-01' );
Query OK, 1 row affected (0.01 sec)

mysql> insert into datetest values( '2003-01-01' );
Query OK, 1 row affected (0.01 sec)

mysql> insert into datetest values( '2004-01-01' );
Query OK, 1 row affected (0.00 sec)

mysql> insert into datetest values( '2005-01-01' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from datetest where year(date_) > (year(now())-1);
+------------+
| date_ |
+------------+
| 2003-01-01 |
| 2004-01-01 |
| 2005-01-01 |
+------------+
3 rows in set (0.03 sec)
--------------------------------------------------

Read more about date and time functions from:

http://www.mysql.com/doc/en/Date_and...functions.html

[Jason Tudisco]

I finally did figure out how to do this.. I post my solution just in
case somebody else finds it useful or has a suggestion for improvment.

SELECT YEAR(startdate) AS year
FROM course_dates
WHERE YEAR(startdate) > (YEAR(NOW())-1)
GROUP BY YEAR(startdate)

Aggro <sp**********@y ahoo.com> wrote in message news:<AC******* *****@read3.ine t.fi>...

Jason Tudisco wrote:

Hello, I not sure if this is the right place to ask this... I am using
mysql.

What I need is a SQL statement that can find what years are in the
database that is greater than last year.

For example...

I have a table full of dates ranging from 2001 to 2005..

I am straining my brain to figure out a SQL statement that can give me
a list of years greater than last year..

In this case I need a list that has..

2003
2004
2005

If anyone could help me.. or give me a clue.. I would appreciate it.

Use mysql function year(date) to extract year from date, and compare
years. For example

--------------------------------------------------
mysql> create table datetest(date_ date);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into datetest values( '2002-01-01' );
Query OK, 1 row affected (0.01 sec)

mysql> insert into datetest values( '2003-01-01' );
Query OK, 1 row affected (0.01 sec)

mysql> insert into datetest values( '2004-01-01' );
Query OK, 1 row affected (0.00 sec)

mysql> insert into datetest values( '2005-01-01' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from datetest where year(date_) > (year(now())-1);
+------------+
| date_ |
+------------+
| 2003-01-01 |
| 2004-01-01 |
| 2005-01-01 |
+------------+
3 rows in set (0.03 sec)
--------------------------------------------------

Read more about date and time functions from:

http://www.mysql.com/doc/en/Date_and...functions.html