By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,876 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,876 IT Pros & Developers. It's quick & easy.

SQL for finding years in dates?

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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**********@yahoo.com> wrote in message news:<AC************@read3.inet.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

Jul 19 '05 #2

P: n/a
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**********@yahoo.com> wrote in message news:<AC************@read3.inet.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

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.