Inserting a Date into Database

Question

I'm trying to retrieve user data form a form and save the data into a database. I'm trying to retrieve the following data: 1) First Name 2) Last Name 3) Major 4) Year

SQL syntax:

CREATE TABLE `tblStudents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `major` varchar(40) NOT NULL,
  `year` date NOT NULL,
  PRIMARY KEY (`id`)
)

I am able to save all but the Year in the database. Right now, the year that is selected is not saved in the database. It looks like this: 0000-00-00.

In the form, a user selects the year from a dropdown menu.

 <select name="year" id="year">
        <option value="2010">2010-06-12</option>
        <option value="2011">2011-06-12</option>
        <option value="2012">2012-06-12</option>
        <option value="2013">2013-06-12</option>
        <option value="2014">2014-06-12</option>

   </select>

The data type for the 'Year' column is date. Am I required to specify something else in order for the date to be saved in the database.

Answer 1

It may depend on the specific database in use, but typically you specify the date as text, such as '2005-10-15'. So, update mytable set somedate = '2010-10-15' will do the trick.

Answer 2

I changed the values in the option tag from "2010" to "2010-06-12", "2011-06-12", 2012-06-12", "2013-06-12"....etc...etc (year-month-day) and was able to save those values to the database.

<select name="year" id="year">
        <option value="2010-06-12">2010-06-12</option>
        <option value="2011-06-12">2011-06-12</option>
        <option value="2012-06-12">2012-06-12</option>
        <option value="2013-06-12">2013-06-12</option>
        <option value="2014-06-12">2014-06-12</option>

   </select>

Answer 3

If you only want to save the year you can take the column type YEAR (or you can take SMALL(4)).

Answer 4

If you only need the YEAR, I don't agree with your last answer of adding a vague month and year.

What I will recommend is to update your table structure to update the column type to YEAR