Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Databases _ Datepicker cant be saved into mysql db

Posted by: ishan Apr 11 2011, 02:31 AM

Hi,

Using jquery datepicker,I have the format of the date returned from Datepicker as (yy-mm-dd) which returns the string as 2011-04-22.

When I want to INSERT it in the MySQL table in the column with datatype as DATE, I get 0000-00-00 saved. Browsing through SO, I found suggestions about FROM_UNIXTIME, but this gives me the date as 1970-01-01.

Any help ?

Posted by: Brian Chandler Apr 11 2011, 04:57 AM

Date formats in MySQL are a pain, but you just have to read the documentation. Start with php.net search for function date.

On the other hand, one simple answer is to avoid the MySQL date stuff (apart from anything else, it is interpreted as local time on the server, which for me is in a funny foreign country, which doesn't even have a simple fixed conversion to GMT). Here's a sample table which I found when I checked how I do it:

CODE

// +--------+-------------+------+-----+---------+-------+
// | Field  | Type        | Null | Key | Default | Extra |
// +--------+-------------+------+-----+---------+-------+
// | ocode  | int(4)      |      | PRI | 0       |       | order number
// | src    | varchar(10) | YES  |     | NULL    |       | supplier ('Epoch' etc.)
// | status | varchar(4)  | YES  |     | NULL    |       | open, pend(ing), canc(elled), sent, recd
// | yyyy   | int(4)      | YES  |     | NULL    |       | ) date created
// | mm     | int(2)      | YES  |     | NULL    |       | )
// | avail  | int(8)      |      |     | NULL    |       | expected delivery date
// +--------+-------------+------+-----+---------+-------+    


You still sort by date, using the YYYY, MM, DD fields separately. It also means you can use your own conventions for things like 'vague dates': I have an "expected" value for ordered stock, which can be a date, or things like 'mid-July 2011'. You don't lose much by not using the MySQL date functions.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)