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 ?

On 26 Jun, 15:23, "Tony B" <tag...@yahoo.co.uk> wrote:
> 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

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

On 26 Jun, 16:04, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.com> wrote:
> 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

Captain Paralytic schreef:
> On 26 Jun, 16:04, Erwin Moller
> <Since_humans_read_this_I_am_spammed_too_m...@spam yourself.com> wrote:
>> 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. ;-)

"Erwin Moller"
<Since_humans_read_this_I_am_spammed_too_much@spam yourself.com> wrote in
message news:4863b009$0$14342$e4fe514c@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

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:ee910cf6-746f-4ab8-a04f-ef12d37bc197@y21g2000hsf.googlegroups.com...
> On 26 Jun, 16:04, Erwin Moller
> <Since_humans_read_this_I_am_spammed_too_m...@spam yourself.com> wrote:
>> 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

On Jun 26, 4:04 pm, Erwin Moller
<Since_humans_read_this_I_am_spammed_too_m...@spam yourself.com> wrote:
> 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.

On 27 Jun, 13:29, "C. (http://symcbean.blogspot.com/)"
<colin.mckin...@gmail.com> wrote:
> On Jun 26, 4:04 pm, Erwin Moller
>
> <Since_humans_read_this_I_am_spammed_too_m...@spam yourself.com> wrote:
> > 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.