Coverting php text string to mysql data format ?

[Tony B]

I have a string in a existing php script which is in the form "dd/mm/yyyy"
and I need to convert it into a suitable format for mysql which is
"yyyy-mm-dd" Is there a neat way of doing this in php ?

[Captain Paralytic]

On 26 Jun, 15:23, "Tony B" <[e-mail]:

I have a string in a existing php script which is in the form "dd/mm/yyyy"
and I need to convert it into a suitable format for mysql which is
"yyyy-mm-dd" Is there a neat way of doing this in php ?

Do it in MySQL instead. Look at the DATE_FORMAT function in the MySQL
manual:

http://dev.mysql.com/doc/refman/5.0/...on_date-format

Furher questions of this sort should be in comp.databases.mysql

[Erwin Moller]

Tony B schreef:

I have a string in a existing php script which is in the form "dd/mm/yyyy"
and I need to convert it into a suitable format for mysql which is
"yyyy-mm-dd" Is there a neat way of doing this in php ?

Hi,

I always do this 'by hand', eg:
$orgDate = "22/06/2008";
$partsArr = explode("/",$orgDate);
$newDate = $partsArr[2]."-".$partsArr[1]."-".$partsArr[0];

If you think the above method sucks, you can also do this:

You can also make a Unix Time Stamp of the original date, and use date()
to parse it to a format you need:

parse a date to UTS:
http://nl.php.net/manual/en/function.strtotime.php

date:
http://nl.php.net/manual/en/function.date.php

Regards,
Erwin Moller

[Captain Paralytic]

On 26 Jun, 16:04, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.comwrote:

Tony B schreef:

I have a string in a existing php script which is in the form "dd/mm/yyyy"
and I need to convert it into a suitable format for mysql which is
"yyyy-mm-dd" Is there a neat way of doing this in php ?

Hi,

I always do this 'by hand', eg:
$orgDate = "22/06/2008";
$partsArr = explode("/",$orgDate);
$newDate = $partsArr[2]."-".$partsArr[1]."-".$partsArr[0];

If you think the above method sucks, you can also do this:

You can also make a Unix Time Stamp of the original date, and use date()
to parse it to a format you need:

parse a date to UTS:http://nl.php.net/manual/en/function.strtotime.php

date:http://nl.php.net/manual/en/function.date.php

Regards,
Erwin Moller

Erwin that all seems an awful lot of work when MySQL supplies the
STR_TO_DATE function specifically to do this job!
http://dev.mysql.com/doc/refman/5.0/...on_str-to-date

[Erwin Moller]

Captain Paralytic schreef:

On 26 Jun, 16:04, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.comwrote:

>Tony B schreef:

>>I have a string in a existing php script which is in the form "dd/mm/yyyy"
and I need to convert it into a suitable format for mysql which is
"yyyy-mm-dd" Is there a neat way of doing this in php ?

Hi,

I always do this 'by hand', eg:
$orgDate = "22/06/2008";
$partsArr = explode("/",$orgDate);
$newDate = $partsArr[2]."-".$partsArr[1]."-".$partsArr[0];

If you think the above method sucks, you can also do this:

You can also make a Unix Time Stamp of the original date, and use date()
to parse it to a format you need:

parse a date to UTS:http://nl.php.net/manual/en/function.strtotime.php

date:http://nl.php.net/manual/en/function.date.php

Regards,
Erwin Moller

Erwin that all seems an awful lot of work when MySQL supplies the
STR_TO_DATE function specifically to do this job!
http://dev.mysql.com/doc/refman/5.0/...on_str-to-date

Yes I know. Or more honest: I expected MySQL had it (I don't use MySQL,
only Postgresql, which of course has it all.)
I can only say I don't trust datehandling.
I live in Europe/Netherland, and over here 05/06/2008 means 5 june 2008
for example.
My server however is configured USA style.
Next country has different notation.

So when I throw the string "2008/06/05" at you, what does it mean?
And what does it mean in Bulgary? Or India?
I always found working with dates very confusing.

When I work on dates I am always double/triple check, especially when
users provide the strings, or they come for some external source.

I once extended an existing employee-time-declare system so their bosses
could accept or reject declared hours for Philips. The users where
traveling all over the world, through timezones, etc. It was a total
confusing disaster, especially because the employees entered their
dates/times themself.
I guess I picked up the habbit of handling dates myself in that time.

Anyway, all excuses. You are right of course.
I don't have to impose my date-paranoia on others. ;-)

Regards,
Erwin Moller

PS: I know my explode-like solution doesn't solve the 2008/06/05
month/day problem.
Nowadays, when I get dates in from clients, I simply avoid using e TEXT
field, but use dropdowns with months/days/years, so they cannot do it
wrong. Oh well, it is HARDER for them to do it wrong. ;-)

[Tony B]

"Erwin Moller"
<Si******************************************@spam yourself.comwrote in
message news:48***********************@news.xs4all.nl...

Tony B schreef:

>I have a string in a existing php script which is in the form
"dd/mm/yyyy" and I need to convert it into a suitable format for mysql
which is "yyyy-mm-dd" Is there a neat way of doing this in php ?

Hi,

I always do this 'by hand', eg:
$orgDate = "22/06/2008";
$partsArr = explode("/",$orgDate);
$newDate = $partsArr[2]."-".$partsArr[1]."-".$partsArr[0];

If you think the above method sucks, you can also do this:

You can also make a Unix Time Stamp of the original date, and use date()
to parse it to a format you need:

parse a date to UTS:
http://nl.php.net/manual/en/function.strtotime.php

date:
http://nl.php.net/manual/en/function.date.php

Regards,
Erwin Moller

Hi,
Thanks for the info. I also found split function which can replace explode
in your code fragment, though slower as use regex.
Tony

[Tony B]

"Captain Paralytic" <pa**********@yahoo.comwrote in message
news:ee**********************************@y21g2000 hsf.googlegroups.com...

On 26 Jun, 16:04, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.comwrote:

>Tony B schreef:

I have a string in a existing php script which is in the form
"dd/mm/yyyy"
and I need to convert it into a suitable format for mysql which is
"yyyy-mm-dd" Is there a neat way of doing this in php ?

Hi,

I always do this 'by hand', eg:
$orgDate = "22/06/2008";
$partsArr = explode("/",$orgDate);
$newDate = $partsArr[2]."-".$partsArr[1]."-".$partsArr[0];

If you think the above method sucks, you can also do this:

You can also make a Unix Time Stamp of the original date, and use date()
to parse it to a format you need:

parse a date to UTS:http://nl.php.net/manual/en/function.strtotime.php

date:http://nl.php.net/manual/en/function.date.php

Regards,
Erwin Moller

Erwin that all seems an awful lot of work when MySQL supplies the
STR_TO_DATE function specifically to do this job!
http://dev.mysql.com/doc/refman/5.0/...on_str-to-date

I also tried this solution, and again it works well. I guess this solution
is clearer and can be extended easily if needed to other formats.
Thanks

[C. (http://symcbean.blogspot.com/)]

On Jun 26, 4:04 pm, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.comwrote:

Tony B schreef:

I have a string in a existing php script which is in the form "dd/mm/yyyy"
and I need to convert it into a suitable format for mysql which is
"yyyy-mm-dd" Is there a neat way of doing this in php ?

<snip>

You can also make a Unix Time Stamp of the original date, and use date()
to parse it to a format you need:

parse a date to UTS:http://nl.php.net/manual/en/function.strtotime.php

Every implementation of strtotime I've used insists on parsing dates
as US format (mm/dd/yy[yy]) rather than dd/mm/yy[yy], regardless of
any locale / TZ settings, to the point where I now avoid using the
function.

Are you saying it now works with dd/mm/yy?

C.

[Captain Paralytic]

On 27 Jun, 13:29, "C. (http://symcbean.blogspot.com/)"
<[e-mail]:

On Jun 26, 4:04 pm, Erwin Moller

<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.comwrote:

Tony B schreef:

I have a string in a existing php script which is in the form "dd/mm/yyyy"
and I need to convert it into a suitable format for mysql which is
"yyyy-mm-dd" Is there a neat way of doing this in php ?

<snip>

You can also make a Unix Time Stamp of the original date, and use date()
to parse it to a format you need:

parse a date to UTS:http://nl.php.net/manual/en/function.strtotime.php

Every implementation of strtotime I've used insists on parsing dates
as US format (mm/dd/yy[yy]) rather than dd/mm/yy[yy], regardless of
any locale / TZ settings, to the point where I now avoid using the
function.

Are you saying it now works with dd/mm/yy?

No he didn't say that. That is why he had the lines:
$orgDate = "22/06/2008";
$partsArr = explode("/",$orgDate);
$newDate = $partsArr[2]."-".$partsArr[1]."-".$partsArr[0];

in his post!

But as I have already said, the MySQL function STR_TO_DATE() is the
correct one to use for this scenario.