[Date] Converting dd/mm/yyyy to YYYY-MM-DD?

[Vincent Delporte]

Hello

My site is hosted on a server in the US, hence set up to use
the mm/dd/yyyy date format instead of the European dd/mm/yyyy.

Also, MySQL stores dates as yyyy-mm-dd, so I need to convert
dd/mm/yyyy to that format. I'm a newbie, and didn't find the answer
through Google :-/

Do I need to call another function in addition to strtotime() to make
this happen?

--------------------
//In the form dd/mm/yyyy
$dateorig = $_POST["dateorig"];

//Doesn't work because server set up to assume mm/dd/yyyy!
$new_dateorig = date("Y-m-d", strtotime($date orig));

print $new_dateorig;
--------------------

Thank you.

[Vincent Delporte]

Re: [Date] Converting dd/mm/yyyy to YYYY-MM-DD?

On Thu, 11 Jan 2007 02:42:55 +0100, Vincent Delporte
<justask@acme.c omwrote:

>Do I need to call another function in addition to strtotime() to make
>this happen?

For those interested, I found the following code that seems to work:

$mydate = $_POST["mydate"];

//Date formated as dd/mm/yyyy
list($d, $m, $y) = preg_split('/\//', $mydate);

$mydate = sprintf('%4d%02 d%02d', $y, $m, $d);
print $mydate;

There may be a better way, such as telling PHP that the dates are
formatted as dd/mm/yyyy instead of mm/dd/yyyy so that strtotime will
work without the above kung-fu, but I don't know.

[Vincent Delporte]

Re: [Date] Converting dd/mm/yyyy to YYYY-MM-DD?

On Thu, 11 Jan 2007 03:57:03 +0100, Vincent Delporte
<justask@acme.c omwrote:

>There may be a better way, such as telling PHP that the dates are
>formatted as dd/mm/yyyy instead of mm/dd/yyyy so that strtotime will
>work without the above kung-fu, but I don't know.

And if you want to do the opposite when reading dates from MySQL and
display them in a web page:

$query = "SELECT name,DATE_FORMA T(mydate,'%d/%m/%Y') FROM mytable";

Maybe it's possible to configure MySQL so that it always spits out
dates as dd/mm/yyyy to avoid using DATE_FORMAT().

[ZeldorBlat]

Re: Converting dd/mm/yyyy to YYYY-MM-DD?


Vincent Delporte wrote:

On Thu, 11 Jan 2007 02:42:55 +0100, Vincent Delporte
<justask@acme.c omwrote:

Do I need to call another function in addition to strtotime() to make
this happen?

>
For those interested, I found the following code that seems to work:
>
$mydate = $_POST["mydate"];
>
//Date formated as dd/mm/yyyy
list($d, $m, $y) = preg_split('/\//', $mydate);
>
$mydate = sprintf('%4d%02 d%02d', $y, $m, $d);
print $mydate;
>
There may be a better way, such as telling PHP that the dates are
formatted as dd/mm/yyyy instead of mm/dd/yyyy so that strtotime will
work without the above kung-fu, but I don't know.

I prefer to keep datetime values as timestamps until the last possible
moment when I format them for display. So you could do something like
this:

$query = "SELECT name, UNIX_TIMESTAMP( mydate) FROM mytable";
//get the value from the query result. It's already a timestamp, so we
don't need strtotime
$my_ts = ... ;
$mydate = date('m/d/Y', $my_ts); //date() does all sorts of nifty stuff

[boclair]

Re: [Date] Converting dd/mm/yyyy to YYYY-MM-DD?

Vincent Delporte wrote:

Hello
>
My site is hosted on a server in the US, hence set up to use
the mm/dd/yyyy date format instead of the European dd/mm/yyyy.
>
Also, MySQL stores dates as yyyy-mm-dd, so I need to convert
dd/mm/yyyy to that format.

--------------------
//In the form dd/mm/yyyy
$dateorig = $_POST["dateorig"];
>
//Doesn't work because server set up to assume mm/dd/yyyy!
$new_dateorig = date("Y-m-d", strtotime($date orig));
>
print $new_dateorig;
--------------------

$dateorig = "03/21/2005";


$new_dateorig = date("Y-m-d", strtotime($date orig));

print $new_dateorig;

reports 2005-03-21.

Louise

[Toby Inkster]

Re: [Date] Converting dd/mm/yyyy to YYYY-MM-DD?

Vincent Delporte wrote:

//Doesn't work because server set up to assume mm/dd/yyyy!
$new_dateorig = date("Y-m-d", strtotime($date orig));

Try:

$dateRE = '/^(\d\d?)[\/\-\.](\d\d?)[\/\-\.](\d\d(\d\d)?\)$/';
$tryStrToTime = TRUE;

if (preg_match($da teRE, $dateorig, $m))
{
if (strlen($m[3])==2)
$m[3] += 39<=(int)$m[3] ? 1900 : 2000;
$date_ts = @mktime(0, 0, 0, $m[2], $m[1], $m[3]);
if ($date_ts!==FAL SE)
$tryStrToTime = FALSE;
}
if ($tryStrToTime)
$date_ts = strtotime($date orig);

This will parse dates in the normal d/m/y format (even allowing for 2
digit years, treating '/00' to '/38' as 2000 to 2038, and '/39' to '/99'
as 1939 to 1999), with various separators allowed (/.-), but if they don't
fit that format, or that doesn't make sense (e.g. month 12), then it
passes the date over to strtotime() which can deal with some very loose
date formats (e.g. 'yesterday', 'next Tuesday').

[Note: if you actually want to specify a date range within AD 10 to AD 99
(i.e. two digit years in the first century AD), you can achieve this by
left-padding the year with zeros. e.g. 01/01/099 for the 1st of January,
99 AD. However, on most operating systems, the timestamp does not have a
range long enough to cover such dates. e.g. 32-bit Unix timestamps run
from 13 Dec 1901 until 19 Jan 2038. Hopefully 64-bit will save us! See

http://en.wikipedia.org/wiki/Year_2038_Problem]

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact

[Vincent Delporte]

Re: [Date] Converting dd/mm/yyyy to YYYY-MM-DD?

On Thu, 11 Jan 2007 04:06:08 GMT, boclair <boclair@bigpon d.net.au>
wrote:

>$dateorig = "03/21/2005";
>
>
>$new_dateori g = date("Y-m-d", strtotime($date orig));
>
>print $new_dateorig;
>
>reports 2005-03-21.

But like I said, it doesn't work for me, because the server on which
the site is hosted assumes dates are mm/dd/yyyy, but we use dd/mm/yyyy
over here, so strtotime() is wrong.

[Vincent Delporte]

Re: Converting dd/mm/yyyy to YYYY-MM-DD?

On 10 Jan 2007 20:02:21 -0800, "ZeldorBlat " <zeldorblat@gma il.com>
wrote:

>I prefer to keep datetime values as timestamps until the last possible
>moment when I format them for display.

Thanks for the idea, but I still have to display dates as dd/mm/yyyy
since this is the normal way to display dates over here.

Isn't there a way to configure MySQL to save dates this way, and avoid
massaging dates between PHP and MySQL? That would also make SELECTs
easier (SELECT * from, instead of having to name all columns just to
call some date function like UNIX_TIMESTAMP( ) or DATE_FORMAT() ).

Thanks.

[Michael Fesser]

Re: Converting dd/mm/yyyy to YYYY-MM-DD?

..oO(Vincent Delporte)

>On 10 Jan 2007 20:02:21 -0800, "ZeldorBlat " <zeldorblat@gma il.com>
>wrote:

>>I prefer to keep datetime values as timestamps until the last possible
>>moment when I format them for display.

>
>Thanks for the idea, but I still have to display dates as dd/mm/yyyy
>since this is the normal way to display dates over here.

Sure, where's the problem?

>Isn't there a way to configure MySQL to save dates this way, and avoid
>massaging dates between PHP and MySQL?

That's not possible and you don't really want that. Storing dates as
DATE or DATETIME allows the database to do a lot of date calculations
and manipulations. That's why such column types exist.

>That would also make SELECTs
>easier (SELECT * from, instead of having to name all columns just to
>call some date function like UNIX_TIMESTAMP( ) or DATE_FORMAT() ).

That's how it's supposed to be. Either let the DB return an already
formatted date or a Unix timestamp so PHP can do the rest. Additionally
you should never use SELECT * in a productive application, but list all
fields explicitly.

Micha

[william]

Re: Converting dd/mm/yyyy to YYYY-MM-DD?

On Thu, 11 Jan 2007 21:44:50 +0100, Michael Fesser wrote:

Additionally
you should never use SELECT * in a productive application, but list all
fields explicitly.

why ?
just curious, because i try to keep the table quite simple (e.g. not a lot
of fields) if there is another reason.
Or is it for security reason (sql injection) ?

[Vincent Delporte]

Re: Converting dd/mm/yyyy to YYYY-MM-DD?

On Thu, 11 Jan 2007 21:44:50 +0100, Michael Fesser <netizen@gmx.de >
wrote:

>That's not possible and you don't really want that. Storing dates as
>DATE or DATETIME allows the database to do a lot of date calculations
>and manipulations. That's why such column types exist.

OK, makes sense. I just wanted to make sure there wasn't an easier way
than converting dates back and forth between PHP and MySQL.

Thanks everyone.

[Michael Fesser]

Re: Converting dd/mm/yyyy to YYYY-MM-DD?

..oO(william)

>On Thu, 11 Jan 2007 21:44:50 +0100, Michael Fesser wrote:
>

> Additionally
>you should never use SELECT * in a productive application, but list all
>fields explicitly.

>
>why ?
>just curious, because i try to keep the table quite simple (e.g. not a lot
>of fields) if there is another reason.
>Or is it for security reason (sql injection) ?

There's a short note in the manual:

| SELECT * is quite suitable for testing queries. However, in an
| application, you should never rely on using SELECT * and retrieving
| the columns based on their position. The order and position in which
| columns are returned does not remain the same if you add, move, or
| delete columns. A simple change to your table structure could cause
| your application to fail.

http://www.mysql.org/doc/refman/5.1/...umn-order.html

Using SELECT * might also cause performance issues and unnecessary
traffic if there are many columns or some which contain a lot of data
(BLOBs for example). Finally there can also be problems or unexpected
results when using a DISTINCT clause.

Micha

[Toby Inkster]

Re: [Date] Converting dd/mm/yyyy to YYYY-MM-DD?

Vincent Delporte wrote:

But like I said, it doesn't work for me, because the server on which
the site is hosted assumes dates are mm/dd/yyyy, but we use dd/mm/yyyy
over here, so strtotime() is wrong.

It does somewhat annoy me that PHP will assume 01/02/03 to be 2 Jan 2003,
when the vast majority of the world's population will take it to be either
1 Feb 2003 or 3 Feb 2001.

India (pop 1100M), Europe (700M), Africa (855M, excluding South Africa),
South America (370M), Pakistan (170M), Russia (145M), Mexico (105M),
Australia (20M) and NZ (5M) all use little-endian DMY dates: that's over
3.5 billion people (63% of the world) using DMY.

China (pop 1300M), Japan (130M), the Koreas (70M), South Africa (50M) and
Nepal (30M) use the big-endian YMD system: that's nearly 1.6 billion
people (25%).

The only significantly populous countries to use MDY regularly are USA
(pop 300M), Philippines (90M) and Canada (30M): less than half a billion
people (6.5%). (And French Canadians often use DMY.)

(If you were wondering what happened to the other 5.5% of the world's
population, they're mostly using the Islamic calendar.)

It's ridiculous that the date formats used by the majority of the world's
people, must be caught as special cases before a date is passed through to
strtotime(). It's not really PHP's fault though -- PHP just follows the
standard GNU "get_date() " function. Still it would be nice if they could
add an extra parameter:

strtotime ( string time [, int now [, int ambiguity]] )

Such that, if the time was ambiguous (12/31/1999 is unambiguously 31 Dec
1999, but 12/1/1999 could be either 1 Dec or 12 Jan) would assume MDY when
ambiguity==0, DMY when ambiguity==1 and YMD when ambiguity==2.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact