Convert mm/dd/yyyy into mysql timestamp?

Question

I've been trying for about two hours to get this working with no luck. I'm trying to convert a date that's entered like 11/18/2012 into a mysql timestamp but everything I've tried just ends up as either 0000-00-00 00:00:00 or NULL in the database :( I'm using PHP + MYSQL so a solution in either would be great

Please also post your attempt, so that we can skip what doesn't work. — Asad Saeeduddin, Nov 29 '12 at 11:46
possible duplicate of [convert string to date php](http://stackoverflow.com/questions/8711952/convert-string-to-date-php) — davidethell, Nov 29 '12 at 11:52
I've tried a bunch of stuff... using mysql I tried UNIX_TIMESTAMP(STR_TO_DATE('$release_date','%m/%d/%y')) and UNIX_TIMESTAMP($release_date) in php I tried $release_date = strtotime($_POST['release_date']); and some other stuff but I don't have the code anymore — Jordan, Nov 29 '12 at 11:55
@Jordan `UNIX_TIMESTAMP(STR_TO_DATE('$release_date','%m/%d/%y'))` returns an integer, so you can't really insert it into a timestamp column. Try `TIMESTAMP(STR_TO_DATE('$release_date','%m/%d/%y'))` — Asad Saeeduddin, Nov 29 '12 at 11:58

Elby · Accepted Answer · 2012-11-29T11:59:34.987

7

Try This

 $release_date=$_POST['release_date'];
    echo date("Y-m-d H:i:s",strtotime($release_date));

edited Nov 29 '12 at 11:59

answered Nov 29 '12 at 11:50

Elby

1,624
3
23
42

2

Thanks! that worked perfectly :) Sorry for the newb question but I've never had to do this before and I spent forever trying to figure it out myself before asking, this is what I used; $release_date = date("Y-m-d H:i:s",strtotime($_POST['release_date'])); Thanks again! :) :) – Jordan Nov 29 '12 at 12:03

score 1 · Answer 2 · answered Nov 29 '12 at 11:48

PHP's DateTime to the rescue!

$datetime = new DateTime($alternativeFormat);
echo $datetime->format('Y-m-d H:i:s'); // mysql format

It's also possible to leave the altering of the data by MySQL, but I advice against it. By using the DateTime object you leave your query open to support other formats aswell.

Asad Saeeduddin · Answer 3 · 2012-11-29T12:01:08.683

1

Use STR_TO_DATE:

SELECT TIMESTAMP(STR_TO_DATE('11/18/2012','%m/%d/%Y'))

http://sqlfiddle.com/#!2/d41d8/4363/0

You stated that you tried:

UNIX_TIMESTAMP(STR_TO_DATE('$release_date','%m/%d/%y'))

The reason this doesn't work is because UNIX_TIMESTAMP's return type is unsigned integer, not TIMESTAMP. This is why you can't insert it into a TIMESTAMP column

edited Nov 29 '12 at 12:01

answered Nov 29 '12 at 11:49

Asad Saeeduddin

46,193
6
90
139

11/18 is clearly m/d, not d/m. Edited. OP would probably be using this in an `INSERT` or `UPDATE` statement, but the point is you replace the bare string in the SQL with a call to STR_TO_DATE('bare string', 'format string'). – Mark Reed Nov 29 '12 at 11:51

score 0 · Answer 4 · answered Nov 29 '12 at 11:47

Have you tried strtotime(): http://php.net/manual/en/function.strtotime.php

Or exploding on '/' and then using mktime(): http://php.net/manual/en/function.mktime.php

$parts = explode('/', $date);
$timestamp = mktime(0, 0, 0, $parts[0], $parts[1], $parts[2]);

Or any of the other suggestions in answers posted here?

score 0 · Answer 5 · answered Nov 29 '12 at 11:51

0

My favorite method:

$date = '10/1/2012';
$mysqlDate = date('Y-m-d', strtotime($date));

answered Nov 29 '12 at 11:51

davidethell

11,708
6
43
63

score 0 · Answer 6 · answered Nov 29 '12 at 11:54

I would try something like this.

$sDate = '11/18/2012';

$aDate = explode('/', $sDate);

$sMySQLTimestamp = sprintf(
   '%s-%s-%s 00:00:00',
    $aDate[2],
    $aDate[0],
    $aDate[1]
);

var_dump($sMySQLTimestamp);
> string(19) "2012-11-18 00:00:00"

Fathah Rehman P · Answer 7 · 2012-11-29T12:07:09.203

0

can you enter that example date as 2012/11/18 ?

if yes use

select convert('2012/11/18' ,DATETIME)

or you can use

 select convert(str_to_date('11/18/2012','%m/%d/%Y'),DATETIME)

edited Nov 29 '12 at 12:07

answered Nov 29 '12 at 11:55

Fathah Rehman P

8,401
4
40
42

score 0 · Answer 8 · answered Nov 29 '12 at 11:59

The correct answer will depend upon exactly what you're trying to do, but in most cases it is a combination of these things:

Use a DateTime object, rather than a string, to represent the timestamp in PHP. (Convert it on input rather than when writing to the database).
Use a database interface that has placeholders, and when filling in a value that's a DateTime, automatically converts them to the appropriate string format

This keeps you from having to convert to, or even know, the native format expected by MySQL.

Convert mm/dd/yyyy into mysql timestamp?

8 Answers8

Linked