date is sent 0000.00.00 to mysql !

[farahZ]

hello, i'm sending date to mysql. when echoing the date in the browser, the date is correctly viewed.

$date=date("m.d.y");
echo $date;

 

but when sending to the database, its viewing there as 0's

is it a problem in the table in the db or the code itself

 

$sql = "INSERT INTO fooddiary (ID, Date, DayTime, FoodName, Calories)
VALUES ($clid, '$date', 'Breakfast', '$food', $cal)
ON DUPLICATE KEY UPDATE FoodName = '$food', Calories=$cal";
$result = mysqli_query($con, $sql) or die(mysqli_error());

other values are sent successfully!!

[jugesh]

Mysql takes date as YYYY-MM-DD format, If your date column  is Date type

 

If you want to insert date in date("m.d.y") format make date column as varchar

[QuickOldCar]

You can use datetime in mysql with a structure like 0000-00-00 00:00:00

 

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

[farahZ]

switching to varchar solved it.. thanks!!

[DavidAM]

Do NOT store dates as VARCHAR. You will not be able to use the mySql date functions, you will have problems with sorting and comparing, etc. etc.

 

Always store dates as DATE (or DATETIME or even TIMESTAMP). The server accepts and returns them as YYYY-MM-DD. You can change the way it displays when you retrieve the data.

 

Would you store numbers as "two hundred fifty-seven and 23/100" just so you can print it on a check? Of course not.

[farahZ]

Hey if I kept it of date type in the db

How can I send it using php code?

Sure I will use it later for comparison

[Barand]

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

 

or better still, use CURDATE() in the query instead of '$date'

Edited May 16, 2013 by Barand