PHP MySQL date functions

Question

I am working with dates, in both PHP as well as MySQL. EVerytime I use to convert date in unix format. But this time I have taken field in DB as date. But issue is it is taking yyyy-mm-dd format. I want to store it in dd-mm-yyyy format. Is this possible if I set default setting of DB. or each time I have to explode the dd-mm-yyyy format in PHP and convert it in YYYY-MM-DD format. Its my first query.

Second query is I wish to fetch the records from today's date. I mean dates after today's date. Like today then tomorrow then so on.... Is it possible to use order by on date field.

The default output display will always be yyyy-mm-dd, but it's up to you to use DATE_FORMAT() in MySQL or date(strtotime()) in PHP to put it in the format you want. It isn't that MySQL stores it as yyyy-mm-dd, just that it outputs it that way and expects it input that way. — Michael Berkowski
– Michael Berkowski, Commented Mar 15, 2012 at 12:30
MySQL's STR_TO_DATE() will also help you convert the date for insertion. — Michael Berkowski
– Michael Berkowski, Commented Mar 15, 2012 at 12:31
But I have to store in DD-MM-YYYY format in my DB...Is it possible... — Sankalp
– Sankalp, Commented Mar 15, 2012 at 12:33
No you don't need to store dd-mm-yyyy. You need to convert dd-mm-yyyy to yyyy-mm-dd before storing it, then convert it to whatever format you wish when pulling it back out. — Michael Berkowski
– Michael Berkowski, Commented Mar 15, 2012 at 12:35

user1237595 · Accepted Answer · 2012-03-15 12:31:38Z

3

Just use:

$date = date('d-m-Y', strtotime($dateFromDB));

That will convert from MySQL DateTime to the format you have specified.

It is possible to order by date fields, e.g.:

SELECT *
FROM table
WHERE date > [yourDate]
ORDER BY date [DESC | ASC]

answered Mar 15, 2012 at 12:31

user1237595

Sign up to request clarification or add additional context in comments.

2 Comments

Sankalp Over a year ago

this['yourdate'] would be in default yyyy-mm-dd format of DB, right??

user1237595 Over a year ago

Ideally, you can use $dateToCompare = date('Y-m-d', strtotime($date)); to convert to the MySQL format if required.

Your Common Sense · Accepted Answer · 2012-03-15 12:35:51Z

0

Your second requirement contradicts with the first one.
If you store your date in dd-mm-yyyy format, you'll be unable to sort your dates.

So - yes, you have to "explode" the dd-mm-yyyy date in PHP or format it any other way. That's not a big deal though. Everyone does it.

answered Mar 15, 2012 at 12:35

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

1 Comment

Sankalp Over a year ago

hmmm... Ya though its possible every time using PHP but then I get confused on query part to find future dates from today... Means I have to pass parameter in YYYY-MM_DD format to SQL QUERY using PHP...

user1027167 · Accepted Answer · 2012-03-15 12:31:14Z

-1

If you have a field of type 'datetime' you can use the MySQL-Command: FROM_UNIXTIME(%d) for conversion. 'order by' should be no problem.

answered Mar 15, 2012 at 12:31

user1027167

4,5088 gold badges36 silver badges42 bronze badges

Comments

Naveen Kumar · Accepted Answer · 2012-03-15 13:12:36Z

-1

Store the date in default format that is yyyy-mm-dd

when you want to display in front end

use the following query to

select otherFields, date_format(dateField,'%d-%m-%Y') from tableName;

For ordering by date

SELECT * FROM tbl 
   ORDER BY date DESC

edited Mar 15, 2012 at 13:12

answered Mar 15, 2012 at 13:05

Naveen Kumar

4,6011 gold badge20 silver badges39 bronze badges

Collectives™ on Stack Overflow

PHP MySQL date functions

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related