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

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@frontiernet.net]
Sent: 19 February 2007 23:10
To: John_Berman@blueyonder.co.uk; 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" <John_Berman@blueyonder.co.uk>
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/mysql?unsub=m...rontiernet.net
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=j...@jewishgen.org




--
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

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) [mailtoavid.Logan@hp.com]
Sent: 20 February 2007 00:19
To: John_Berman@blueyonder.co.uk
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: david.logan@hp.com
**** _/ _/ _/ _/ **** 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@blueyonder.co.uk]
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:mickalo@frontiernet.net]
Sent: 19 February 2007 23:10
To: John_Berman@blueyonder.co.uk; 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" <John_Berman@blueyonder.co.uk>
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/mysql?unsub=m...rontiernet.net
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=j...@jewishgen.org




--
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=david.logan@hp.com