Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table people
- ( id int auto_increment primary key,
- name varchar(100) not null,
- birthdate timestamp not null -- uses a TIMESTAMP as requested
- );
- truncate table people;
- insert people (name,birthdate) values ('John',current_timestamp());
- insert people (name,birthdate) values ('Kim','1997-10-16');
- insert people (name,birthdate) values ('James','1997-10-14');
- insert people (name,birthdate) select 'Mary','1990-01-04';
- insert people (name,birthdate) select 'Sally','1980-06-30';
- select * from people;
- +----+-------+---------------------+
- | id | name | birthdate |
- +----+-------+---------------------+
- | 1 | John | 2016-10-15 19:04:41 |
- | 2 | Kim | 1997-10-16 00:00:00 |
- | 3 | James | 1997-10-14 00:00:00 |
- | 4 | Mary | 1990-01-04 00:00:00 |
- | 5 | Sally | 1980-06-30 00:00:00 |
- +----+-------+---------------------+
- select id,
- name,
- birthdate,
- datediff(now(),date_add(birthdate,interval 19 year)) as diffInDays
- from people
- having diffInDays>0;
- +----+-------+---------------------+------------+
- | id | name | birthdate | diffInDays |
- +----+-------+---------------------+------------+
- | 3 | James | 1997-10-14 00:00:00 | 1 |
- | 4 | Mary | 1990-01-04 00:00:00 | 2841 |
- | 5 | Sally | 1980-06-30 00:00:00 | 6317 |
- +----+-------+---------------------+------------+
Advertisement
Add Comment
Please, Sign In to add comment