SQL Date functions

[Graffixnerd]

I want to calculate somebody date using his/her Date of Birth which is located on the database column name(DOB)..which sql function do i use and how do i use it on the Sqlquery..please help

[d5e5]

I want to calculate somebody date using his/her Date of Birth which is located on the database column name(DOB)..which sql function do i use and how do i use it on the Sqlquery..please help

To write a query that shows somebody's date of birth you don't need a function. Assuming that DOB is defined as a DATE datatype you can select it using the default format, which is YYYY-MM-DD.

mysql> select * from people where name = 'somebody';
+----+----------+------------+
| id | name     | dob        |
+----+----------+------------+
|  2 | somebody | 1974-04-18 | 
+----+----------+------------+
1 row in set (0.00 sec)

[urtrivedi]

To format your date output you may use date_format() function. refer mysql manual for details.

[Graffixnerd]

Sorry i made a mistake ,i want to calculate sumbudy years (hw old the person is )using their date of birth (that is located in the column DOB)and the current year.....perhaps (current year - DOB)....i would lyk a sql syntax for that..

[d5e5]

Sorry i made a mistake ,i want to calculate sumbudy years (hw old the person is )using their date of birth (that is located in the column DOB)and the current year.....perhaps (current year - DOB)....i would lyk a sql syntax for that..

If you subtract the year of birth from the current year you sometimes will get the correct age but you may be off by a year. Why? For example, I was born in nineteen fifty-one. 2010 - 1951 = 59. Am I 59 years old? No, because I haven't had my birthday yet this year.

The following query gives a better result.

SELECT	name,
	dob,
	YEAR(CURRENT_DATE)
	- YEAR(dob)
	- CASE WHEN MONTH(CURRENT_DATE)
		> MONTH(dob)
		THEN 0
		WHEN MONTH(CURRENT_DATE)
		< MONTH(dob)
		THEN 1
		WHEN DAYOFMONTH(CURRENT_DATE)
		< DAYOFMONTH(dob)
		THEN 1
		ELSE 0 END AS age
FROM people;

Which gives the following results:

+-----------+------------+------+
| name      | dob        | age  |
+-----------+------------+------+
| Cable Guy | 1951-02-28 |   59 | 
| somebody  | 1974-04-18 |   36 | 
| John Doe  | 1965-07-28 |   44 | 
| David     | 1951-10-01 |   58 | 
+-----------+------------+------+

[griswolf]

There is a reasonably complete discussion of this issue here: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html ... be sure to read the user comments section at the bottom where there are some carefully worked out examples, including a complete answer for this question.