• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

mySQL dates

I have a (UK) date...

$start = date("d/m/Y H:i:s");

and I update/insert this into mysql..
$startmysql = date("Y-d-m H:i:s", $start);

and pull it back with:
$start = date("d/m/Y H:i:s",$startmysql);

However, neither of the mySQL functions currently are working?
0
ARCglide
Asked:
ARCglide
  • 5
  • 5
  • 3
1 Solution
 
German_RummCommented:
Hi ARCglide,

You need to have a column of type DATETIME

---
German Rumm.
0
 
ARCglideAuthor Commented:
Yep start is set as datetime
0
 
German_RummCommented:
ARCglide,

Well, another thing: DATETIME column should look like YYYY-MM-DD, you are currently inserting YYYY-DD-MM
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ARCglideAuthor Commented:
Basically, $start is passed in within the date format: "d/m/Y H:i:s"
and I need to convert it to: "Y-d-m H:i:s" to add to mySQL.
0
 
German_RummCommented:
ARCglide,

Another thing (probably the most important, sorry that I overlooked it).
date() does not accept formatted date as a second parameter, it accepts number of seconds.
use date('Y-m-d H:i:s'); // or date('Y-m-d H:i:s', time())
and you will be alright :-)
0
 
German_RummCommented:
ARCglide,

ah, ok.
then you need to first extract date from your format:
list ($day, $month, $year, $hour, $minute, $seconds) = sscanf('%d-%d-%d %d:%d:%s');
date('Y-m-d H:i:s', mkdate($hour, $minute, $seconds, $month, $day, $year));
0
 
ARCglideAuthor Commented:
$start is passed in within the date format: "d/m/Y H:i:s"
and I need to convert it to: "Y-m-d H:i:s" to add to mySQL

(However as pointed out I can't use strtotime() - as the date is in the wrong format)
0
 
German_RummCommented:
ARCglide,

Grrrr, i make a lot of typos today. Here is correct version:
    $date = '09/05/2005 14:39:01';
    list ($day, $month, $year, $hour, $minute, $seconds) = sscanf($date, '%d/%d/%d %d:%d:%d');
    echo date('Y-m-d H:i:s', mktime($hour, $minute, $seconds, $month, $day, $year));
0
 
ldbkuttyCommented:
You can use strtotime(). Try this example:

<?php
 echo date("Y-m-d H:i:s", strtotime("09/05/2005 13:40:10"));
?>
0
 
ldbkuttyCommented:
Sorry I was wrong. strtotime() takes MM/DD/YYYY format, not DD/MM/YYYY. Neglect my last comment please.
0
 
ldbkuttyCommented:
Here's MySql based solution:

SELECT STR_TO_DATE('09/05/2005 13:40:10', '%d/%m/%Y %H:%i:%s');

returns: 2005-05-09 13:40:10
0
 
ARCglideAuthor Commented:

try that with the 25th of 5 - 2005 ...

 echo date("Y-m-d H:i:s", strtotime("25/05/2005 13:40:10"));  ---> "2007-01-05 13:40:10" -??-

-----------------
Points to German_Rumm
0
 
ARCglideAuthor Commented:
Thanks everyone - all is now good !
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now