CODEWITHSUNDEEP
phpmysqldatetime
markzzz
markzzz

Reputation: 48045

MySql & PHP - How to convert a string to DATETIME

I have some data (insered by users) like :

  1. 10-12-2010
  2. XX-12-2010
  3. 10-XX-2010
  4. 10-12-XXXX

(Notice that I add XX (or XXXX) when the data is not insered.)

How can I convert these String to DATETIME by using MySql syntax?

Thanks

UPDATE

$day=(is_numeric($_POST['day']) && $_POST['day'] > 0 && $_POST['day'] < 32) ? $_POST['day'] : '00';
$month=(is_numeric($_POST['month']) && $_POST['month'] > 0 && $_POST['month'] < 13) ? $_POST['month'] : '00';
$year=(is_numeric($_POST['year']) && $_POST['year'] > 1999 && $_POST['year'] < (date("Y")+1)) ? $_POST['year'] : '0000';            
$data=$year."-".$month."-".$day;
$mysqlDate=date('Y-m-d', strtotime($data));
echo $mysqlDate;

There is a problem : if data is *empty-12-2010" it print 2010-11-30. Why?

Upvotes: 0

Views: 7223

Answers (6)

JakeParis
JakeParis

Reputation: 11210

There is a problem : if data is *empty-12-2010" it print 2010-11-30.

Why? Because strtotime is reading that as 00-12-2010, which doesn't exist, so it's converting it to the day before Dec 1, which is numerically Dec 0, or in reallife, Nov 30 !

Upvotes: 1

JakeParis
JakeParis

Reputation: 11210

If you're sure you don't want to require all the date fields, just fill them with zero if they don't exist, like this:

$month = ($_POST['month'] == '') ? '00' : $_POST['month'];

otherwise, if you're worried not about blank dates, but about non-standard input, you could do:

$month = (is_numeric($_POST['month']) && $_POST['month'] > 0 && $_POST['month'] < 13) ? $_POST['month'] : '00'

$date = date('Y-m-d G:i:s', strtotime($_POST['dataThatUserEntered']));

should do the trick, no?

Just make sure you validate the user input, otherwise you may find that $date = '1970-01-01 00:00:00 if they enter something crazy.

Upvotes: 1

acme
acme

Reputation: 14866

In MySQL's syntax you can use the function STR_TO_DATE():

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y'); // -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y'); // -> '2013-05-01'

Upvotes: 2

petingo0
petingo0

Reputation: 29

Well, you could use date(), using a format and string.

$mysqlDate = date( 'Y-m-d', $stringDate );

Upvotes: 0

s3v3n
s3v3n

Reputation: 8456

Mysql does the convertion automatically. You can see accepted formats here. But you should be aware of xxx and make mandatory that fields.

Upvotes: 0

Sarfraz
Sarfraz

Reputation: 382909

You can use php's strtotime function.

strtotime — Parse about any English textual datetime description into a Unix timestamp

You should check out the kind of format you can get to work with it for your string.

Upvotes: 1

Related Questions