SQL help day mth year in the database.

[alvin567]

I have three fields in the database for a date of birth,

 

day | month | year

 

Output the date type

 

If possible

 

I want to do the manipulation in the database and output the age.

[alvin567]

my point is I want to output the ages.

[gizmola]

Why would you use 3 seperate columns when you can use a single DATE column?  Furthermore, if you do use a date column you can utilize built-in mysql functions to calculate age.  See http://www.gizmola.com/blog/archives/107-Calculate-a-persons-age-in-a-MySQL-query.html

 

 

[alvin567]

gizmo,there are three of these columns in the database,I am trying to use mysql queries,

can i cast these 3 columns as date type instead?

[gizmola]

gizmo,there are three of these columns in the database,I am trying to use mysql queries,

can i cast these 3 columns as date type instead?

 

Yes, you can manufacture a mysql date using TO_DATE()

[alvin567]

gizmo,

 

I am thinking of strictly using mysql to manipulate the data.

Currently my environment is php/mysql

[gizmola]

I meant to say:  STR_TO_DATE()

[alvin567]

ok,so i use a str_to_date()

then how do I calculate the age?

[gizmola]

ok,so i use a str_to_date()

then how do I calculate the age?

 

Did you read the blog article?

[alvin567]

SELECT *, STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(birthdate), '-', DAY(birthdate)) ,'%Y-%c-%e') AS birthday FROM user;

 

I think you mean this portion?

 

[alvin567]

Gizmo:

 

I have manage to solve the issue already,thanks

 

SELECT * , YEAR( CURDATE( ) ) - year - IF( STR_TO_DATE( CONCAT( YEAR( CURDATE( ) ) ,  '-', month,  '-', day ) ,  '%Y-%c-%e' ) > CURDATE( ) , 1, 0 ) AS age

FROM member

LIMIT 0 , 300;

[gizmola]

Great.  There are numerous ways to calculate the age.  If you want to simplify your query a bit you can use this as well:

 

SELECT *, FLOOR(DATEDIFF(CURRENT_DATE, STR_TO_DATE(CONCAT(year, '-', month, '-', day), '%Y-%c-%e'))/365.25) as age FROM member

 

 

[alvin567]

but I though datediff is for mssql only?

[ignace]

Read the manual: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html DATEDIFF() is a mysql function.

[alvin567]

I have a questions:

 

how do I count the number of values in a database using a group by conditions?

[ignace]

SELECT col, COUNT(col)
FROM table
GROUP BY col

[Barand]

Mysql also has a COUNT() function - not just M$SQL