469,366 Members | 2,222 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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

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($dateorig));

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

Thank you.
Jan 11 '07 #1
12 47878
On Thu, 11 Jan 2007 02:42:55 +0100, Vincent Delporte
<ju*****@acme.comwrote:
>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%02d%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.
Jan 11 '07 #2
On Thu, 11 Jan 2007 03:57:03 +0100, Vincent Delporte
<ju*****@acme.comwrote:
>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_FORMAT(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().
Jan 11 '07 #3

Vincent Delporte wrote:
On Thu, 11 Jan 2007 02:42:55 +0100, Vincent Delporte
<ju*****@acme.comwrote:
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%02d%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

Jan 11 '07 #4
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($dateorig));

print $new_dateorig;
--------------------
$dateorig = "03/21/2005";
$new_dateorig = date("Y-m-d", strtotime($dateorig));

print $new_dateorig;

reports 2005-03-21.

Louise
Jan 11 '07 #5
Vincent Delporte wrote:
//Doesn't work because server set up to assume mm/dd/yyyy!
$new_dateorig = date("Y-m-d", strtotime($dateorig));
Try:

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

if (preg_match($dateRE, $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!==FALSE)
$tryStrToTime = FALSE;
}
if ($tryStrToTime)
$date_ts = strtotime($dateorig);

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

Jan 11 '07 #6
On Thu, 11 Jan 2007 04:06:08 GMT, boclair <bo*****@bigpond.net.au>
wrote:
>$dateorig = "03/21/2005";
$new_dateorig = date("Y-m-d", strtotime($dateorig));

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.
Jan 11 '07 #7
On 10 Jan 2007 20:02:21 -0800, "ZeldorBlat" <ze********@gmail.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.
Jan 11 '07 #8
..oO(Vincent Delporte)
>On 10 Jan 2007 20:02:21 -0800, "ZeldorBlat" <ze********@gmail.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
Jan 11 '07 #9
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) ?
Jan 11 '07 #10
On Thu, 11 Jan 2007 21:44:50 +0100, Michael Fesser <ne*****@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.
Jan 11 '07 #11
..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
Jan 11 '07 #12
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

Jan 12 '07 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by ToysNTreasures | last post: by
11 posts views Thread by Lauren Quantrell | last post: by
2 posts views Thread by jofo | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.