Thanks for the advice Its actually just a year of birth and the year type field is perfect apart from the fact that it wont work with anything pre 1900
Regards John B -----Original Message----- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: 20 February 2007 00:19 To: [EMAIL PROTECTED] Cc: Mike Blezien; mysql@lists.mysql.com Subject: RE: Year - Field type Why not keep the date of birth as a standard date field and extract the fields you need using the DATE_FORMAT function? eg. DATE_FORMAT(date_of_birth, '%Y') mysql> \u test Database changed mysql> create table test_dates (a int, mydate date); Query OK, 0 rows affected (0.29 sec) mysql> describe test_dates; +--------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | | mydate | date | YES | | NULL | | +--------+---------+------+-----+---------+-------+ 2 rows in set (0.03 sec) mysql> insert into test_dates SET a=1, mydate="1887-10-12"; Query OK, 1 row affected (0.10 sec) mysql> select * from test_dates; +------+------------+ | a | mydate | +------+------------+ | 1 | 1887-10-12 | +------+------------+ 1 row in set (0.00 sec) mysql> insert into test_dates SET a=1, mydate="1987-10-12"; Query OK, 1 row affected (0.10 sec) mysql> select * from test_dates order by mydate; +------+------------+ | a | mydate | +------+------------+ | 1 | 1887-10-12 | | 1 | 1987-10-12 | +------+------------+ 2 rows in set (0.00 sec) mysql> select DATE_FORMAT(mydate,'%Y') as year FROM test_dates ORDER BY year; +------+ | year | +------+ | 1887 | | 1987 | +------+ 2 rows in set (0.00 sec) mysql> Regards --------------------------------------------------------------- ********** _/ ********** David Logan ******* _/ ******* ITO Delivery Specialist - Database ***** _/ ***** Hewlett-Packard Australia Ltd **** _/_/_/ _/_/_/ **** E-Mail: [EMAIL PROTECTED] **** _/ _/ _/ _/ **** Desk: +61 8 8408 4273 **** _/ _/ _/_/_/ **** Mobile: +61 417 268 665 ***** _/ ****** ****** _/ ******** Postal: 148 Frome Street, ******** _/ ********** Adelaide SA 5001 Australia i n v e n t --------------------------------------------------------------- -----Original Message----- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 February 2007 9:59 AM To: 'Mike Blezien'; mysql@lists.mysql.com Subject: RE: Year - Field type Mickalo I gave that a go now I have another issue I can only enter years 1900 onwards so when I enter 1887 it changes the value to 0 John B -----Original Message----- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: 19 February 2007 23:10 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Year - Field type have you try using the datatype YEAR for you table field/column ? Mickalo ----- Original Message ----- From: "John Berman" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Monday, February 19, 2007 11:45 AM Subject: Year - Field type > Hi > > Using mysql4 > > > Sure this is an easy one a field in my dbase is year of birth, its always a > 4 digit number, for some reason Im failing to sort by the field in my > results, it was originally a varchar field so I updated it to int but still > no luck. Pointers appreciated. > > Regards > > John Berman > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.412 / Virus Database: 268.18.2/692 - Release Date: 18/02/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
