Solved

Format - and - Unformat a MySQL datetime

Posted on 2004-09-01
6
529 Views
Last Modified: 2013-12-12
Hi, I am struggling with converting mysql datetime formats into dd-mm-yyyy hh:mm:ss format
I have a small web app (PHP) that displays a list of arrival and departure times for aircraft
The format in the database is YYYY-MM-DD hh:mm:ss but I want to display it as dd-mm-yyyy hh:mm:ss
When I try to convert it  (using a timestamp of 1975-01-01 10:00:01) I get a display of 1-1-1970 10:32:55
I also need to convert this format back so I can enter it into the database
Please help!!!!
0
Comment
Question by:maunded
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 36

Expert Comment

by:Zyloch
ID: 11959743
Hi

You can try something like this:

mysql_query("SELECT DATE_FORMAT(the_date,'%D,%M,%Y %T') FROM table_name WHERE this='that'");

More information on date_format
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

Regards,
Zyloch
0
 
LVL 1

Author Comment

by:maunded
ID: 11959865
Heres what I have come up with so far:

function ConvFromMySQL($sqldate) {
      $format= "d-m-Y h:i:s";
$mydate = date($format,strtotime($sqldate));
return $mydate;
};
This is fine, it convers the datetime field to d-m-Y h:i:s no problem.
Converting back the other way to allow for the insert to work properly is a problem...can someone tell me why this dosent work?

function ConvToMySQL($phpdate) {
      $format= "Y-m-d h:i:s";
$mydate = date($format,strtotime($phpdate));
return $mydate;
};

Thanks
0
 
LVL 36

Expert Comment

by:Zyloch
ID: 11959966
I can't be sure. Try doing this:

$format="YmdHis";
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 1

Author Comment

by:maunded
ID: 11960009
I got it:
function ConvFromMySQL($sqldate) {
      $format= "d-m-Y H:i:s";
$mydate = date($format,strtotime($sqldate));
return $mydate;
};

function ConvToMySQL($phpdate) {

$array = preg_split("/[\s\/\.\-:]/", $phpdate);
$mydate = "$array[2]-$array[1]-$array[0] $array[3]:$array[4]:$array[5]";
return $mydate;
};

Its messy I think, but it works!
0
 
LVL 36

Accepted Solution

by:
Zyloch earned 500 total points
ID: 11960024
Nice job, heh, It actually isn't very messy at all
0
 
LVL 1

Author Comment

by:maunded
ID: 11960031
Accepted yours since you were the first (and only) to answer :)
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
deprecated mysql extensions 1 55
Form submit takes only for one form 23 54
Windows 10, 64 bit WAMP - won't start 9 32
Format Date 7 28
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question