473,387 Members | 1,282 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,387 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 48150
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
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

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.