Member Avatar for ajay_p5

Hello people,

Well The problem that I am facing is:
I have a date in string format like 30 Jun 2009, how can I insert it into MYSQL table as DATE data type?

I created the table and declare data type for Date column as DATE, when I insert 30 Jun 2009 as a value into the table, it becomes 00:00:00. What should I do to avoid this error. Please suggest. Thanks

Ajay

  • 3 Contributors
  • 2 Replies
  • 289 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by baki100

Dani AI

Generated

Short checklist and root cause
MySQL DATE columns store and display dates in the canonical "YYYY-MM-DD" form; if you hand the server a different textual format it may not parse it and can end up storing a zero/invalid date or the time part you weren't expecting. First, confirm the column type so you are actually writing into a DATE column (not TIME or DATETIME): use DESCRIBE or SHOW CREATE TABLE for the table. (dev.mysql.com)

Preferred fix (application-side conversion)
Parse the human text into a proper SQL date before inserting. For example, in PHP use DateTime::createFromFormat to parse the incoming string and then format it to Y-m-d; send that value as a parameter in a prepared statement so MySQL receives "2009-06-30" (or whatever the equivalent is). This avoids locale surprises and silent conversions on the server. See the PHP DateTime docs for parsing and formatting details. (php.net)

Server-side option (when you cannot change app code)
MySQL provides a server-side parser you can call inside SQL to convert a string to a date, but it only succeeds when the format you supply matches the string exactly; on failure it can return NULL and raise warnings. If you follow 's suggestion to use MySQL parsing, first run a safe SELECT using that function to verify the result before doing INSERT/UPDATE in production. (dev.mysql.com)

Troubleshooting tips
If you still get zeros or 00:00:00, check the SQL mode (SELECT @@sql_mode;) — strict modes will make parsing errors raise errors instead of silently inserting zero dates — and check SHOW WARNINGS after the statement. Also re-check that your INSERT actually sends a Y-m-d string (DATETIME columns will show a time portion default of 00:00:00 if only a date is provided). These quick checks will narrow whether the problem is format, column type, or server mode. (dev.mysql.com)

Example quick commands (run in mysql client):

DESCRIBE your_table;
SELECT @@sql_mode;
SHOW WARNINGS;

Recommended Answers

All 2 Replies

Member Avatar for almostbob

depending on the language used as the front end to the mysql back end,
php $date=strtotime('string');
asp $date=DateTime.Parse('string');
in sql getdate($date)

don't use those codes look em up, I havent, so there will be errors

sql date format sux, is explained here http://www.tizag.com/sqlTutorial/sqldate.php
hugely sux
personal viewa unix timestamp is easier apologies
other people with less rigid literal minds have little trouble with setting up the processing to run queries on sql date types

Edited by almostbob because: n/a
commented: Well said +11
Member Avatar for baki100

try to convert it as a date in the insert statement by using this function:
str_to_date('30 Jun 2009','%d %b %Y')

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.