convert php date to mysql format

Question

I have a date field in php which is using this code     date   mysql real escape string   POST  intake date       How do I convert this to MySql format 0000-00-00 for inclusion in db  Is it along the lines of  date  Y-m-d  strtotime  date    The reason I ask  is because I have tried variations of this and I cannot seem to get it to work  Either displays as 1970 or some other variation of that  Many thanks

User · Answer

This site has two pretty simple solutions - just check the code, I provided the descriptions in case you wanted them - saves you some clicks.

http://www.richardlord.net/blog/dates-in-php-and-mysql

1.One common solution is to store the dates in DATETIME fields and use PHPs date() and strtotime() functions to convert between PHP timestamps and MySQL DATETIMEs. The methods would be used as follows -

$mysqldate = date( 'Y-m-d H:i:s', $phpdate );
$phpdate = strtotime( $mysqldate );

2.Our second option is to let MySQL do the work. MySQL has functions we can use to convert the data at the point where we access the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp and FROM_UNIXTIME will convert from PHP timestamp to DATETIME. The methods are used within the SQL query. So we insert and update dates using queries like this -

$query = "UPDATE table SET
    datetimefield = FROM_UNIXTIME($phpdate)
    WHERE...";
$query = "SELECT UNIX_TIMESTAMP(datetimefield)
    FROM table WHERE...";

User · Answer

You are looking for the the MySQL functions FROM UNIXTIME   and UNIX TIMESTAMP     Use them in your SQL  e g    mysql gt  SELECT UNIX TIMESTAMP NOW      -----------------------    UNIX TIMESTAMP NOW       -----------------------               1311343579    -----------------------  1 row in set  0 00 sec   mysql gt  SELECT FROM UNIXTIME 1311343579    ---------------------------    FROM UNIXTIME 1311343579     ---------------------------    2011-07-22 15 06 19          ---------------------------  1 row in set  0 00 sec

User · Answer

If you know the format of date in   POST intake date  you can use explode to get year   month and time and then concatenate to form a valid mySql date  for example if you are getting something like 12 15 1988 in date you can do like this       date   explode   POST  intake date              mysql date    date 2   -  date 1   -  date 0     though if you have valid date date  y-m-d   strtotime  date    should also work

User · Answer

Where you have a posted date in format dd mm yyyy  use the below    date   explode        POST  posted date      new date    date 2   -   date 1   -   date 0     If you have it in mm dd yyyy  just change the second line    new date    date 2   -   date 0   -   date 1

User · Answer

PHP 5 3 has functions to create and reformat at DateTime object from whatever format you specify    mysql date    2012-01-02        date in Y-m-d format as MySQL stores it  date obj   date create from format  Y-m-d   mysql date    date   date format  date obj   m d Y    echo  date    Outputs   01 02 2012   MySQL can also control the formatting by using the STR TO DATE   function when inserting updating  and the DATE FORMAT   when querying    php date    01 02 2012    update query    UPDATE  appointments  SET  start time    STR TO DATE       php date         m  d  Y      query    SELECT DATE FORMAT  start time    m  d  Y   AS  start time  FROM  appointments

User · Answer

If intake date is some common date format you can use date   and strtotime     mysqlDate   date  Y-m-d H i s   strtotime   POST  intake date        However  this will only work if the date format is accepted by strtotime    See it s doc page for supported formats

User · Answer

date   mysql real escape string   POST  intake date       1  If your MySQL column is DATE type    date   date  Y-m-d   strtotime str replace  -         date       2  If your MySQL column is DATETIME type    date   date  Y-m-d H i s   strtotime str replace  -         date       You haven t got to work strototime    because it will not work with dash - separators  it will try to do a subtraction     Update  the way your date is formatted you can t use strtotime    use this code instead    date    02 07 2009 00 07 00    date   preg replace     d 2     d 2     d 4   s          3- 2- 1  4    date   echo  date    Output   2009-07-02 00 07 00

User · Answer

There is several options    Either convert it to timestamp and use as instructed in other posts with strtotime   or use MySQL   s date parsing option

User · Answer

function my date parse  date            if   preg match      d      d      d        date   m                   return false            day    m 1            month    m 2            year    m 3            if   checkdate  month   day   year                   return false           return   year- month- day

[php] convert php date to mysql format

Examples related to php

Examples related to mysql