ansaurus

Question

How to search dob in db is in the yyyy-mm-dd strucuture. But i have to compare only with yyyy of the field using mysql.

Answer 1

+4 A:

select * from table where year(dob) = '2010';

cherouvim 2010-06-16 06:39:33

thanks chrerovium. but could please explain how year(dob) works

Fero 2010-06-16 06:45:15

It extracts only the year part from a specified date (in this case it is the "dob" column). Have a look at http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

cherouvim 2010-06-16 06:46:54

thanks for your advice chrerovium.

Fero 2010-06-16 06:55:23

Note: This works, but will be slow for large sets, as the function prevents use of an index. Having date as a foreign field into a "dates" table would be more efficient, but also more hassle to implement (as date ops would require at least a join more).

Piskvor 2010-06-16 06:58:04

@Piskvor: of course it's a full table scan. Joining dates to another table is an overkill though. A better strategy would probably be to denormalize and repeat the year(dob) in another column with an index on it.

cherouvim 2010-06-16 08:04:32

@cherouvim: the "overkill" label very much depends. I've had this problem before and denormalizing was problematic (in other words, coders didn't update the other, denormalized fields -> inconsistent data); I'd say denormalizing or not is up for discussion (although I admit that having a date in a foreign table does look kind of silly).

Piskvor 2010-06-16 08:46:35

ansaurus

tags:

views:

answers:

How to search dob in db is in the yyyy-mm-dd strucuture. But i have to compare only with yyyy of the field using mysql.

related questions