CODEWITHSUNDEEP
phpmysql
jeremykrall
jeremykrall

Reputation: 181

Best way to insert a date into MySQL

I am getting a text file dump that I am processing into MySQL via PHP and the date format looks like "Jul 2012" or "Dec 2012", etc. I want to insert these into the database in a way that I could then select records in the future by date. Is there any way to convert these to the right format prior or a way to get SQL to automatically convert via a function?

Thanks so much.

Upvotes: 2

Views: 422

Answers (4)

Techie
Techie

Reputation: 45124

Best way to do is generate the data-time according to MySQL's standard format : date("Y-m-d H:i:s")

Below is a conversion

$datetime = strtotime('Dec 2012');
$mysqldate = date("m/d/y", $datetime);

Upvotes: 0

anon
anon

Reputation:

This: 

<?php
echo date("Y/m/d") . "<br>";
echo date("Y.m.d") . "<br>";
echo date("Y-m-d");
?>

Will out put something like:

2009/05/11
2009.05.11
2009-05-11

Try something before, simplly asking...sometimes that helps

Upvotes: 0

C. E.
C. E.

Reputation: 10627

If I remember it right, the format for a datetime SQL column is

date( "Y-m-d H:i:s", strtotime( $date ) );

Where $date is for example Dec 2012.

Upvotes: 0

Jason McCreary
Jason McCreary

Reputation: 73031

Is there any way to convert these to the right format prior or a way to get SQL to automatically convert via a function?

Keep it at the DB level with MySQL's STR_TO_DATE().

For example:

SELECT STR_TO_DATE('Dec 2012','%b %Y');

Upvotes: 3

Related Questions