Jump to content

Formating Date so MySQL Will Except It


alan_k

Recommended Posts

Hello,

I am writing a page that will except some info from user and and get submitted as form. I wanted the date included in the form data so I

wrote this little snippet to insert the current date when form is submitted:

      <li>         <input type="hidden" id="date" name="Date_val" value="date">           <script type="text/javascript">              document.getElementById('date').value = Date();           </script>                   </li>  

Now when I print out my form All I get is a nonsense date 11--00-01 printed in my table. Looking at the

_POST info this is what is getting sent to MySQL:

 

Array ( [formID] => 42796558181164 [Date_val] => Wed Oct 22 2014 17:50:43 GMT-0400 (Eastern Standard Time) [FName_val] => xvxc [LName_val] => xcvxc [q4_department] => Art [q15_reach] => x [q5_roomNumber] => [q6_email6] => [q12_pleaseSpecify12] => Array ( [0] => HR ) [q8_explainProblem] => [Tkt_Status_val] => Open )

I'm assuming that is why I get gibberish as my output. I have the MySql object defined as a simple date format. Can anyone tell me how I should format the date so it will output correctly?

Thanks...

Edited by alan_k
Link to comment
Share on other sites

These are the date formats that MySQL accepts:

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html

 

String and Numeric Literals in Date and Time Context. MySQL recognizes DATE values in these formats:

  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.
  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
  • As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

  • As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45', and '2012@12@31 11^30^45' are equivalent.
    The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.
    The date and time parts can be separated by T rather than a space. For example, '2012-12-31 11:30:45' '2012-12-31T11:30:45' are equivalent.
  • As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
  • As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

 

 

My recommendation is not to use date or datetime fields. Use an INT field and store a UNIX timestamp. They're easy to operate with and you can choose whichever output format you like.

Link to comment
Share on other sites

Thanks for the reply but I'm not quite following you when you say "Use an INT field and store a UNIX timestamp". I am not familiar with how you would go about doing this. Is there no way to convert the Date() function output to a YYYY-MM-DD format that MySQL will understand? Seems like something there would be a lot of demand for?

Link to comment
Share on other sites

You can format a date however you want to format it. Look at the methods for date objects, you can get the various parts and build a date string in any format your heart desires:https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/DateAs far as the int goes, when I store dates in MySQL I don't use the native date or datetime column types, I use an integer. When I want to store a date, in PHP I use a function like time() to get the current Unix timestamp (or mktime to get the timestamp for a particular time). I store the integer timestamp instead of a string date because it is much quicker and easier to do things like compare 2 integers or add and subtract than it is to compare, add, or subtract 2 date strings. The timestamp can be converted back into a readable date string when it needs to be displayed for a person to see.In general though, it is much more useful to get date information on the server instead of in the browser with Javascript. When you use Javascript then you are getting the date and time of the clock on the user's computer, whatever they have that set to (including whatever time zone they're in). When you get dates and times with PHP it is always the server's time, so it is always constant. Trying to have the browser send a date and time is really only useful if you're trying to figure out the time difference between the user's settings and the server.

Link to comment
Share on other sites

Here's the code that worked for me:

<li>         <input type="hidden" id="date" name="Date_val" value="date">           <script src="jscripts/jquery.min.js" type="text/javascript"></script>           <script type="text/javascript">              date_tda = new Date().toISOString().slice(0, 19).replace('T', ' ');              document.getElementById('date').value = date_tda;           </script>                   </li>  

Thanks for everyone's input. You guys are very helpful. Is there a way to mark a question answered?

Edited by alan_k
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...