Change Date Format in PHPMyAdmin

transfield

Hello,
I'm uploading a tab delimited text file into my MySQL database using PHPMyAdmin. I have a field(in my text file) called date in which the date format is MM/DD/YYYY. However, the default date format in PHPMyAdmin is 0000-00-00 which I assume is YYYY/MM/DD.

How do I change the date format in PHPMyAdmin from the current default to MM/DD/YYYY ?

Thanks a lot for your advice.

neroag

try here ;-)

http://uk2.php.net/manual/en/function.date.php

transfield

Thank you for your reply, neroag.

I don't think I explained myself clearly.

The date format in the date field of my tab delimited text file is MM/DD/YYYY. The date format in the date field of my PHPMyAdmin is YYYY/MM/DD.

My question is how do I change the date format in PHPMyAdmin from the current default to MM/DD/YYYY ?

As a last resort, I can do a search & replace on my text file to change the date format before uploading it but this will create other complications in my data processing so I prefer not to do this.

abc123

Think you need to have a look at DATE_FORMAT(date, format) function for mysql.

This isn't really anything to do with phpmyadmin but rather mysql date format. Check out the mysql documentation for using the above function.

Hope this helps.

transfield

Thank you for your reply abc123.

Regardless of whether it's a phpmyadmin or mysql issue, I still need to create a field with the correct date format before I can upload my text file. I'm using the following code to create the field. So how do I tell mysql or phpmyadmin to create the date field in the format that I desire?

$query="CREATE TABLE houses (id int(6) NOT NULL auto_increment,date date NOT NULL,PRIMARY KEY (id))";

abc123

I've had a good look around but can't find anything promising. As far as I can see you can't really change the date format in mysql.

If I had your problem I'd be looking at two possible solutions. Both of which are probably not ideal but it's all I can think of.

1 - use the explode function on each date in your file and then just insert into the mysql table in the format yyyy-mm-dd.

2 - Would it be acceptable to have three columns in your table called 'day', 'month' and 'year'? That way you could store the date as an integer.

Hope this is some help. I'd be interested to know if anyone knows of a way to change the mysql date format though.

transfield

Thank you very much for understanding my situation abc123.

Yes, the explode looks promising. I appreciate your replies on this matter.

Warm Regards.

transfield

Hi abc123,
After doing some reading here & there I discovered that it cannot be done :-( I got the answer from http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html which says

Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

Thank you for your alternative suggestion.

Warm Regards.

Houdini

<?php 
$myinput='12/15/2005';
$sqldate=date('Y-m-d',strtotime($myinput));
echo $sqldate; 
 ?>

It works for me

abc123

Houdini. Thats a neat little solution. I'll have to remember that one. 🙂

Houdini

Make it a function and put it in a functions include. Then just call mysqldate().

kemnet

Hi again, SO i followed what Houdini said so my user can now enter the date as d/m/y
but where / how do i flip it around again so results from my table comes out as d/m/y n not as y/m/d

if (!empty($_POST["f_name"]) && !empty($_POST["birth_d"])) {
       $f_name=$_POST["f_name"];
       $birth_d=$_POST["birth_d"];


//$myinput='12/15/2005'; 
$birth_d=date('Y-m-d',strtotime($birth_d)); 



     $stmt = $this->db->prepare('SELECT id,card_num,birth_d, FROM school WHERE f_name=? AND birth_d=?');



     $stmt->bind_param("ss",$f_name,$birth_d);

$stmt->execute();
$stmt->bind_result($id,$school,$birth_d,$f_name);

bradgrafelman

kemnet;10989172 wrote:
where / how do i flip it around again so results from my table comes out as d/m/y n not as y/m/d

A general answer would be "anywhere before you actually output it." One more specific answer might be "in the SQL query itself."

For example, rather than simply SELECT'ing the data in the birth_d column directly, use MySQL's DATE_FORMAT() function to re-format the date in whatever format you'd like.

kemnet

$stmt = $this->db->prepare('SELECT id,card_num,DATE_FORMAT(birth_d,%e%m%Y), FROM school WHERE f_name=? AND birth_d=?');

bradgrafelman

See the manual for examples of DATE_FORMAT() in a query; the second parameter should be a string (thus you're missing single quotes around it).

kemnet

$stmt = $this->db->prepare('SELECT id,card_num,DATE_FORMAT('birth_d','%e %m %Y'), FROM school WHERE f_name=? AND birth_d=?')

i got this from the manual but it doesnt work,
-From this point on is the birth_d column still "known" as birth date?
cause i have thi later

$stmt->bind_result($id,$birth_d,$f_name);

bradgrafelman

kemnet;10989179 wrote:

$stmt = $this->db->prepare('SELECT id,card_num,DATE_FORMAT('birth_d','%e %m %Y'), FROM school WHERE f_name=? AND birth_d=?')

Two problems with that code snippet:

You now have the first parameter being a string as well, but as the manual states DATE_FORMAT() expects the first parameter to be the actual date you want to format. The string "birth_d" is not a date, so I doubt you want to try and format it as such. Instead, you should be referencing the column birth_d (e.g. don't add quotes around it as if it was a string).
The above error won't matter anyway since you also now have a parse error. Look at the color-coding of the code - you've got unescaped single quotes appearing inside a string delimited with single quotes.

kemnet;10989179 wrote:
-From this point on is the birth_d column still "known" as birth date?
cause i have thi later
$stmt->bind_result($id,$birth_d,$f_name);

Well, no, the column name is the entire 'DATE_FORMAT(...' string itself. However, you can easily add a column alias after it so that it's easier to reference.

Simply add 'AS alias_name' (without the quotes) after the thing you want to rename in the query. Also note that the 'AS' keyword is optional, so you could just have 'DATE_FORMAT(...) alias_name' as well.

kemnet

oh ok sorry, i had birth_d in quotes but id actually figured on using the alias an it still broken. an im wondering if its cause of the
$birth_d=date('Y-m-d',strtotime($birth_d));
line

if (!empty($_POST["f_name"]) && !empty($_POST["birth_d"])) {
       // Put parameters into local variables
      #Query on name and birthdate 

   $f_name=$_POST["f_name"];
   $birth_d=$_POST["birth_d"];

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


   $stmt = $this->db->prepare('SELECT id,class_num, DATE_FORMAT(birth_d,'%e %m %Y') AS birth,f_name FROM school WHERE f_name=? AND birth_d=?');

     $stmt->bind_param("ss", $f_name,$birth_d);

     $stmt->execute();
    $stmt->bind_result($id,$class_num,$birth,$f_name);

bradgrafelman

You still haven't addressed error #2 I noted above, which should be obvious based on the error messages PHP is generating (since you've got display_errors or log_errors set to On and error_reporting set to E_ALL.... right??).

kemnet

Too be honest, im not running this on mamp or wamp or those programs, im writing a program for a console that accesses a webserver. so im writing this php in my webserver's file so i doubt i get that handling.

Edited: i also should mention. i only get the error when i add dAte format(birth_d,etc etc)