Dating Advice

[RelaxoRy]

So I like this girl and..

harrrranyway,

I have birth dates in a table stored as 11/07/1982 or 7/5/1974 etc.
Now there are 7150 records and I cannot really alter the date format
to conform with the mysql YYYY-MM-DD format. Reason being I need to
search by age, ie, BETWEEN 2 dates, which if they're in the DD/MM/YYYY
format it wont work.

Is there a way to :
a) easily change the format of the data in there
b) mysql has some sort of command telling it that the date is actually
in DD/MM/YYYY and to do its magic and work as if it was the norm.

I will be adding new entries to the data. If it's a) I can format the
date on the fly and input it into the database. If it's b) I will let
the data stay as it is

Help appreciated
Ry

[Erwin Moller]

RelaxoRy wrote:

So I like this girl and..

My advise would be to bluntly ask het out for a nice romantic movie.
Maybe a good restaurant first?

Listen good to what she says, and give her a lot of attention.

Never try any funny touching the first date, no matter how difficult that is
for you.
Don't even try to kiss, except of course when she starts.

Call her the next day to tell her what a great time you had.
Do it again next week.

Good luck!

Regards,
Erwin Moller

[noSpam]

RelaxoRy wrote:

So I like this girl and..

harrrranyway,

I have birth dates in a table stored as 11/07/1982 or 7/5/1974 etc.
Now there are 7150 records and I cannot really alter the date format
to conform with the mysql YYYY-MM-DD format. Reason being I need to
search by age, ie, BETWEEN 2 dates, which if they're in the DD/MM/YYYY
format it wont work.

Is there a way to :
a) easily change the format of the data in there
b) mysql has some sort of command telling it that the date is actually
in DD/MM/YYYY and to do its magic and work as if it was the norm.

I will be adding new entries to the data. If it's a) I can format the
date on the fly and input it into the database. If it's b) I will let
the data stay as it is

Help appreciated
Ry

I you want some help then give us a clue:-

1) What sort of table - FLAT ASCII file, MySQL table, Progresql table
etc etc
2) if its a table in a relational database do you mean you are storing
the date as a string ?
3) If its a date column in a relational database why can't you do the
age comparison you want to ?

[Michael Fesser]

.oO(RelaxoRy)

I have birth dates in a table stored as 11/07/1982 or 7/5/1974 etc.
Now there are 7150 records and I cannot really alter the date format
to conform with the mysql YYYY-MM-DD format.
Why not? There's no reason to not use native date formats if they are
available.
Reason being I need to
search by age, ie, BETWEEN 2 dates, which if they're in the DD/MM/YYYY
format it wont work.
With properly stored dates such calculations are quite easy.
Is there a way to :
a) easily change the format of the data in there
b) mysql has some sort of command telling it that the date is actually
in DD/MM/YYYY and to do its magic and work as if it was the norm.

Go for a). It's not trivial, but can be done with two queries. First you
have to change the values from DD/MM/YYYY to YYYY-MM-DD. Assuming the
field is of some string type, you can use MySQL's string functions to
split and reassemble the dates (might become a bit difficult if dates
are stored without leading zeros, make sure you backup your DB first).
After that you can change the column type to DATE with an ALTER query.

Micha

[relaxory]

Yeah thats what i am led to believe. It's just a pain you know :) I
thought there was some function to make MySQL be able to read my
current data as it would a date field :)

Ohwell, time to play with strings!

Michael Fesser <ne*****@gmx.net> wrote in message news:<77********************************@4ax.com>. ..

.oO(RelaxoRy)

I have birth dates in a table stored as 11/07/1982 or 7/5/1974 etc.
Now there are 7150 records and I cannot really alter the date format
to conform with the mysql YYYY-MM-DD format.

Why not? There's no reason to not use native date formats if they are
available.

Reason being I need to
search by age, ie, BETWEEN 2 dates, which if they're in the DD/MM/YYYY
format it wont work.

With properly stored dates such calculations are quite easy.

Is there a way to :
a) easily change the format of the data in there
b) mysql has some sort of command telling it that the date is actually
in DD/MM/YYYY and to do its magic and work as if it was the norm.

Go for a). It's not trivial, but can be done with two queries. First you
have to change the values from DD/MM/YYYY to YYYY-MM-DD. Assuming the
field is of some string type, you can use MySQL's string functions to
split and reassemble the dates (might become a bit difficult if dates
are stored without leading zeros, make sure you backup your DB first).
After that you can change the column type to DATE with an ALTER query.

Micha

[NC]

RelaxoRy wrote:

I have birth dates in a table stored as 11/07/1982 or 7/5/1974 etc.
In a TEXT or VARCHAR field, I presume?
Now there are 7150 records and I cannot really alter the date
format to conform with the mysql YYYY-MM-DD format. Reason
being I need to search by age, ie, BETWEEN 2 dates, which if
they're in the DD/MM/YYYY format it wont work.
Ah, yet another early-stage design mistake that is proving
costly to fix later... :)
Is there a way to :
a) easily change the format of the data in there

No, but you can do something else. Let's say your birth date
column is named DOB. So you can:

1. Add a new column (type DATE) to your existing table and
name it, say, `born`.
2. Run something like this:

$select = 'SELECT id, DOB from mytable';
$result = mysql_query($select);
while ($record = mysql_fetch_array($result)) {
$id = $record['id'];
list($day, $month, $year) = explode('/', $record['DOB']);
$update = "UPDATE mytable SET born='$year-$month-$day' " .
"WHERE id=$id";
mysql_query($update);
}

3. Delete the old DOB column.
4. Rename `born` into `DOB`.

Cheers,
NC

[Ewoud Dronkert]

On 23 Feb 2005 02:10:53 -0800, relaxory wrote:

It's just a pain you know :)

What, 2 simple queries?

alter table t1 add d2 as date;
update t1 set d2=concat_ws('-',right(d1,4),mid(d1,3,2),left(d1,2));
--
Firefox Web Browser - Rediscover the web - http://getffox.com/
Thunderbird E-mail and Newsgroups - http://gettbird.com/

[NC]

Ewoud Dronkert wrote:

What, 2 simple queries?

alter table t1 add d2 as date;
update t1 set d2=concat_ws('-',right(d1,4),mid(d1,3,2),left(d1,2));

The second one will probably make a mess because, according
to the original poster, leading zeros are not always present.
This is why I proposed a PHP script earlier...

Cheers,
NC

[Michael Fesser]

.oO(Ewoud Dronkert)

On 23 Feb 2005 02:10:53 -0800, relaxory wrote:

It's just a pain you know :)

What, 2 simple queries?

alter table t1 add d2 as date;
update t1 set d2=concat_ws('-',right(d1,4),mid(d1,3,2),left(d1,2));

It's not that simple if the dates are not stored with leading zeros, but
still possible with a single (but rather long) query. And if you do the
UPDATE first then you don't even need a new column, you can simply
change the type to DATE after the update.

Micha

[Ewoud Dronkert]

On 24 Feb 2005 09:09:07 -0800, NC wrote:

The second one will probably make a mess because, according
to the original poster, leading zeros are not always present.

Ah yes sorry, glanced over his post and saw the "DD/MM/YYYY". A script
would be fine I guess. Or maybe:

alter t1 add d2 as date, i1 as tinyint, i2 as tinyint;
update t1 set i1=locate('/', d1), i2=locate('/', d1, 4);
update t1 set d2=concat_ws('-', substring(d1, i2+1), substring(d1, i1+1,
i2-i1-1), left(d1, i1-1));

Not tested; not sure if column values i1 and i2 can be used like this in
the 2nd update.

--
Firefox Web Browser - Rediscover the web - http://getffox.com/
Thunderbird E-mail and Newsgroups - http://gettbird.com/