Format Date Fields Using MySQL DATE_FORMAT()

Discussion

1. julesj

   I like to do this kind of transformations on the PHP side, with date(), mktime() and the like. Does anybody know which method is quickest?
2. david

   @julesj: If you’re pulling from a database, you might as well fit them into the query. If not, continue to use date().
3. Jeff Hartman

   I typically store all dates in an integer field. That was a habit I got into when most hosts and clients were on MySQL 4.x and there was no datediff function (from what I recall).

   A drawback is that it is impossible to read as an integer when it is stored in the database. However, I find it simple to use with PHP functions. No worries about whether or not the query is formatted properly, etc.
4. Jade Robbins

   Unix timestamps for the win!
5. david

   For the record, I’ve referenced this page for the fourth time today at work.
• dfgsdfg

  http://www.mysqlformatdate.com does all the work for you, all you do is choose the format you
6. Lewis Walsh

   I always use integers to store dates as unixtime. I find that this way it’s easier to do arithmetic on the values before formatting using PHP’s date() function.
7. Juan Gotti

   This is a great post, Thanks.

   This programming world has been really hard for me because I first needed to lear the language (English) and the apply it to programming, hehe tough task.

   My question is what if I want to have the date in Spanish, what’s the most practical way to crear a custom date function to use it in other languages?

   Thanks in advance!
8. david

   @Juan: I don’t think MySQL is the best match for that. I would try writing a PHP function to do it. Here’s a good starting point:

   http://php.net/date
9. Juan Gotti

   Thanks!
10. richard

    I’ve been wondering about what the differences are between timestamp and datetime. are there any big differences between the two?
11. Bob

    SELECT DATE_FORMAT(time, ‘%Y%c%e’) …..

    Can anyone tell me why this snippet of code errors everytime due to the percentage signs. It does, everytime, yet every example that is ever illustrated uses this convention.

    What gives? If I remove the percentages I get the correct results in other scenarios, just not the correct format.

    Thanky.
12. Tashi

    Hi, I am trying to compare a group of dates with current date and find if its previous so that i could mark it as expired. I tried with strtotime() but it can compare only the current years date and converts the date of other year’s to current years.
    I hope to hear from someone else…Thank you.
13. mysql format date

    http://www.mysqlformatdate.com does all the work for you, all you do is choose the format you want.
14. Rodrigo Espinoza

    @Juan Gotti: hey hello juan this could be prob little late just wanted to give you an option to do this

     $sql = query ("SELECT DATE_FORMAT(date, '%a %d %b %Y %h::%i:%S %p') AS date FROM usuarios WHERE id = '$login'");
     $row = fetch ($sql);
     $eng = array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Mon','Tue','Wed','Thu','Fri','Sat','Sun');
     $esp = array('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic','Lunes','Martes','Miercoles','Jueves','Viernes','Sábado','Domingo');
     $row['date'] = str_replace($eng,$esp,$row['date']);
    
• Rql

  Hola!, excelente, gracias!! :)
15. fatfreddy

    Another gem from the meister – I should have a favorites subfolder called “Dave’s invaluable snippets”

    Cheers again Dave, keep em coming!
16. Punjab Engineer

    u have given good information. but pls tell us code as to how date should be inserted in database, particularly if users input date in dd-mm-yyyy format.
17. goyal

    pls give clarification about the difference between date, time and timestamp datatypes in mysql.
18. aneesh

    Hi
    thanks for explaining this
19. byh

    Thanks for the great post. Im using this to fetch data from a website.
20. Mark

    Thanks, David. This is a great resource! Say, any idea how one might format AND order by date? I’m getting very unexpected results.
21. Kyaw Lwin Phyo

    Hi, I’ve tried
    “SELECT ‘Date:’, NOW() into OUTFILE ‘E:\\SUBCON\\EDWHFiles\\Threshold\\time.txt'”
    but I’m getting this value “Date: Wed\ 30\ Nov\ 2011\ 04:59:36\ PM”.
    why is this happening????
    Can anyone explain and correct me??
    Thanks.
22. Kyaw Lwin Phyo

    SELECT ‘Date:’, DATE_FORMAT(NOW(),’%a %d %b %Y %r’) into OUTFILE ‘E:\\SUBCON\\EDWHFiles\\Threshold\\time.txt’

    sorry the query is like this, not as the above.
23. Aneeq

    I have found a good solution on the site below. Works like charm for me.

    http://phphelp.co/2012/03/28/how-to-print-all-the-months-and-years-between-two-dates-in-php/

    Or

    http://addr.pk/ac8b
24. wizard

    Awesome Post, Extremely Thankful to you for your invaluable time and effort.
25. pratik

    thanks it saved time
26. Shalini

    Wonderful example,i t was same what i was searching for.

    Thanks a lot :)