[SOLVED] Save Date to table field date type

[jimlawrnc]

I have a calendar picker on a form that fills a text box with  XX/XX/XXXX  date format.  If I run a insert statement  the date field in the database shows 0000-00-00.  If i change the field type to varchar  the correct format displays. 

 

What is the correct way to save a date to a mysql database?  Furthermore to query on the date  how about a select * from db_name where servicedate >= $startdate  and  servicedate >= $enddate    I supose this will not work

 

Any pointers?

[pocobueno1388]

"0000-00-00" is the best format, but you can do it how you want.

 

To format the date how you are wanting, you would do this

$date = date("Y/m/d");

 

Now if you inserted $date into a DATE field in the database, I'm pretty sure it will come out the format you set it in.

[jimlawrnc]

From POST 

 

$serviceDate =  htmlspecialchars($_POST['datBegin']);

$newDate = date("Y-m-d");
$servicedate = $newDate;
print $servicedate;

 

prints to the page  2007-11-09   

 

when i run a select * from dbname in phpmyadmin  the date is  0000-00-00

[Barand]

Store as type DATE, format YYYY-MM-DD

 

SELECT * FROM table WHERE servicedate BETWEEN '2007-10-01' AND '2007-10-31'

 

d/m/y or m/d/y cannot be used for comparisons, sorting etc

[jimlawrnc]

Sweet  even though it will take getting used to the format of YYYY/MM/DD 

 

This works for me