CODEWITHSUNDEEP
phpmysql
Sankalp
Sankalp

Reputation: 17

PHP MySQL date functions

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.

Upvotes: 0

Views: 703

Answers (4)

Naveen Kumar
Naveen Kumar

Reputation: 4591

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

Upvotes: -1

Your Common Sense
Your Common Sense

Reputation: 157828

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.

Upvotes: 0

Ingmar Boddington
Ingmar Boddington

Reputation: 3500

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]

Upvotes: 3

user1027167
user1027167

Reputation: 4438

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.

Upvotes: -1

Related Questions