[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]

On Thu, 11 Jan 2007 02:42:55 +0100, Vincent Delporte
<ju*****@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]

On Thu, 11 Jan 2007 03:57:03 +0100, Vincent Delporte
<ju*****@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]

Vincent Delporte wrote:

On Thu, 11 Jan 2007 02:42:55 +0100, Vincent Delporte
<ju*****@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]

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]

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]

On Thu, 11 Jan 2007 04:06:08 GMT, boclair <bo*****@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]

On 10 Jan 2007 20:02:21 -0800, "ZeldorBlat " <ze********@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]

..oO(Vincent Delporte)

>On 10 Jan 2007 20:02:21 -0800, "ZeldorBlat " <ze********@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]

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) ?