469,270 Members | 1,026 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

Coverting php text string to mysql data format ?

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 ?
Jun 27 '08 #1
8 2861
On 26 Jun, 15:23, "Tony B" <tag...@yahoo.co.ukwrote:
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
Jun 27 '08 #2
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

Jun 27 '08 #3
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
Jun 27 '08 #4
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. ;-)
Jun 27 '08 #5
"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

Jun 27 '08 #6

"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
Jun 27 '08 #7
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.
Jun 27 '08 #8
On 27 Jun, 13:29, "C. (http://symcbean.blogspot.com/)"
<colin.mckin...@gmail.comwrote:
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.
Jun 27 '08 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by adrian GREEMAN | last post: by
4 posts views Thread by news | last post: by
6 posts views Thread by Kevin Chambers | last post: by
13 posts views Thread by sonald | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.