I have a database table where I have set the properties of one field to default to datetime and not null. The date I receive is 12/4/2007 with no time. Do I need to do something else? Thanks.

To insert the date into the MySQL database it should be formatted in the expected YYYY-MM-DD format.

You have not specified if the date you receive is from a file or if a user has typed it into a webpage form field.

If a user has typed it into a webpage form field, you should 1) use javascript to validate that the date entered is valid, and in the correct format, then 2) change the formatting before inserting it into the MySQL database.

By way of an example, here are PHP solutions for the UK date format (it would be simple to change these to work for the US date format):

Howdy... I think datetime is yyyy-mm-dd hh:mm:ss as yellowmarker mentions, so I just read/write MySQL db info that way and use PHP to get what I want. I'm new to both PHP and MySQL so don't know if the way I do it as the same as what was posted, but I parse the datetime into its component parts using substr() and then use mktime() function to convert it to unix epoch time. Once it is in unix epoch time, I can use php's date() function to get just about any format I want.. even using words instead of just digits if I want. Here is my code for converting my MySQL timestamp-14 field that I read into my php variable $whenmodified...

$year = substr ($whenmodified, 0, 4);
$month = substr ($whenmodified, 4, 2);
$day = substr ($whenmodified, 6, 2);
$hour = substr ($whenmodified, 8, 2);
$min = substr ($whenmodified, 10, 2);
$sec = substr ($whenmodified, 12, 2);

$whenmodified_epochtime = mktime($hour, $min, $sec, $month, $day, $year);
$whenmodified_date = date ("m/d/Y", $whenmodified_epochtime);
$whenmodified_time = date ("g:i:s a", $whenmodified_epochtime);

steve, the mysql DATE_FORMAT function can do all of that in one line

exdter, are you sure you're using mysql? because mysql does not allow you to set a default using a function, and furthermore, it will accept a date string like '12/4/2007' but it will interpret it as april 20, 2012

DATE_FORMAT
Good to know, thanks. Since what I work on is web-based, I just find it easier to do it in PHP. Right now, I'm still in the learning stage and just getting data in/out of MySQL as PHP $variables... Plus, I usually just grab all of a record's fields in one simple SELECT command. I'd have to compose another $sql command string just for that field if I wanted to do that in SQL and then have to have another SQL statement if I wanted it some other format. Converting the timestamp to unix epoch gives me the flexibility to show it in just about any format using date() on the fly.

Again, I'm just a newb, though, so I may change techniques as I become more experienced, and I'm sure there will be times ahead when I'm going to want to use DATE_FORMAT, so thanks again.
--ST

Thanks for everyone's reply. I had my field set as datetime default and it was working. The datetime does not come from a web application. When the user clicks on submit on a web app, the date and time is supposed to insert automatically when the info gets uploaded into the MySQL table with the current date and time as default. Does this make sense?

in that case the web app is supplying the date and time, right?

You can do it as rudy guesses you are doing and manually insert the current date and time from your application, but if you want the field automatically updated whenever the record is modified, you can use a timestamp field instead. Timestamp varies a little depending upon MySQL version, but my guess is that if you change your datetime field to a timestamp field, it'll work the way you want.

Hope this helps!
--ST

yes, but be aware, there is a big difference between a default value that is defined in the CREATE TABLE statement (and which you are allowed to define as the value of a function such as CURRENT_DATE in every database system except mysql) and the mysql TIMESTAMP datatype which updates itself every time the row is touched