473,320 Members | 1,859 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

[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 48142
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Scott Morford | last post by:
I am developing a weed management database for the preserve I work on. One of the queries I'm working on will allow the user to run a query and see which weed patches have NOT been treated in the...
4
by: Greg Iocco | last post by:
Simple problem, but I've checked the newsgroups and couldn't find an answer. On the main swithboard is a command button to open a report. The report I built is based off a query, and the query...
1
by: Rob | last post by:
How can I create a validation rule for only allowing two specific dates but with variable years: 1/1/yyyy or 12/1/yyyy so only the dates 1/1/2004 or 1/1/1999 or 12/1/2001 etc. I tried...
1
by: ToysNTreasures | last post by:
Hi, I'm working on a class project in which I have to create a simple hotel reservation database using Access. I've created a couple of tables and queries that allow a user to determine room...
2
by: Sun | last post by:
Hi, I display date in asp.net pages using user defined format: mydate.text=Format(dr("MyDate"), "MMM. d, yyyy"). It works fine for all pages except one. In that page, I built a dataAdapter,...
11
by: Lauren Quantrell | last post by:
I already figured out (the hard way) I need to convert all my date parameters into USA format before executing my stored procedures where dates are used as parameters. (Format(StartDate, "m/d/yyyy...
2
by: jofo | last post by:
Hello All, I have a problem. I inherited a database where someone used DATE as a table field. I actually need to use the Date() function in order to set the date. Everytime I try to use the...
2
by: rpboll | last post by:
Is there a function that stamps the FISCAL Date (Starting from October)? I am trying to generate a Fiscal Date and an AutoNumber for a key field. Thanks for any suggestions. RBollinger
3
by: Robert | last post by:
I need to set up a query that will pick out records for the current winter season. I.e., each season runs from October 1 until March 31. The catch is, the year can't be hard coded. So, if the...
8
by: MLH | last post by:
Sometimes it works and sometimes it crashes. If I want "Today is " & Date$ & "." to appear in a query field, why might it work sometimes and not others? Would I be better to call a FN? Say,...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.