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:David.Logan@stripped]
Sent: 20 February 2007 00:19
To: John_Berman@stripped
Cc: Mike Blezien; mysql@stripped
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: david.logan@stripped
**** _/ _/ _/ _/ **** 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:John_Berman@stripped]
Sent: Tuesday, 20 February 2007 9:59 AM
To: 'Mike Blezien'; mysql@stripped
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:mickalo@stripped]
Sent: 19 February 2007 23:10
To: John_Berman@stripped; mysql@stripped
Subject: Re: Year - Field type
have you try using the datatype YEAR for you table field/column ?
Mickalo
----- Original Message -----
From: "John Berman" <John_Berman@stripped>
To: <mysql@stripped>
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/mysql?unsub=1
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1
--
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/mysql?unsub=1
