CODEWITHSUNDEEP
phpmysqlsqldatetime
Gabriel Ferraz
Gabriel Ferraz

Reputation: 632

Insert date in specific format into MySQL db

I need to insert the current date in the following format into a TIMESTAMP column in a MySQL db: d-m-Y

As of now I am using SQL NOW(), which returns the date as Y-m-d. Because I am using AJAX to display the data I cannot format the returned result using $date_returned->format(d-m-Y). Therefore I need to insert the date in the format that I will display on my AJAX call.

I tried to insert the date using the following functions:

 1) date('d-m-Y');    
 2) (new \DateTime())->format('Y-m-d');

I understand these two functions do pretty much the same thing but I was not sure what else I should try.

MySQL threw the following error for both dates:

Error : (1292) Incorrect datetime value: '-2014' for column 'msg_date' at row 1

I am guessing this should be an easy fix but I can't figure out what is wrong. I tried both TIMESTAMP and DATETIME on MySQL's end but neither worked. (I need it to be TIMESTAMP though).

Any suggestion is welcome!

Upvotes: 0

Views: 339

Answers (3)

thomasrutter
thomasrutter

Reputation: 117333

The MySQL error message indicated that you had the date format the wrong way around.

Year must go first, then month, then day, as in:

date('Y-m-d')  // right

In your first example, you have

date('d-m-Y')  // wrong

In one of your examples above, you have it right, but you say you got the same response, so I assume that was not what you actually tried.

Another thing to note is that a MySQL TIMESTAMP column stores both a date and time. It's valid to give MySQL just a date (MySQL will just leave the time at zero), but if you have no need to store a time, you may as well make the column DATE instead of TIMESTAMP.

If you want to display your dates as d-m-Y then by all means do so, but they need to be sent to MySQL as Y-m-d.

Upvotes: 1

Logan Wayne
Logan Wayne

Reputation: 5991

If your msg_date column's structure is DATETIME or TIMESTAMP, the date format should be:

YYYY-MM-DD HH:MM:SS

which can be formatted through PHP like this:

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

Or if you already have a date, and you want it to convert to that format, we can use strtotime():

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

For more date format, check this link.

Upvotes: 1

Manojkumar
Manojkumar

Reputation: 40

$newdate= date('Y-m-d', strtotime('10-09-2015'));

or if you want current time just use

$now = date('Y-m-d');

Upvotes: 1

Related Questions