0

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

4
Contributors
5
Replies
6
Views
7 Years
Discussion Span
Last Post by griswolf
0

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)
0

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..

0

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 | 
+-----------+------------+------+
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.