Skip to main content
A
Anonymous
August 14, 2009
Answered

Input into MySQL - Date field

  • August 14, 2009
  • 1 reply
  • 1676 views

Hi everyone,

I've got a simple form with a date input field, the data goes into a MySQL database, column formatted to 'DATE'.

GetSQLValueString($_POST['projstart'], "date"),

However, when I try to submit the form,it tells me:

Incorrect date value: '04/08/2009' for column 'projstart' at row 1

I'm guessing this is due to '04/08/2009', being viewed as a string, not a date.

How do I change this to input into the database, as a DATE.???

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO tbl_projects (projtitle, projstart, projdue, FK_custid, FK_langsid, projcontact, projanalysis, projtype, projstatus, projurl) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['projtitle'], "text"),
                       GetSQLValueString($_POST['projstart'], "text"),
                       GetSQLValueString($_POST['projdue'], "text"),
                       GetSQLValueString($_POST['FK_custid'], "int"),
                       GetSQLValueString($_POST['FK_langsid'], "text"),
                       GetSQLValueString($_POST['projcontact'], "text"),
                       GetSQLValueString($_POST['projanalysis'], "text"),
                       GetSQLValueString($_POST['projtype'], "text"),
                       GetSQLValueString($_POST['projstatus'], "text"),
                       GetSQLValueString($_POST['projurl'], "text"));

This topic has been closed for replies.
Correct answer jon8

Before the date gets inserted/updated, use this function:

function mysqlDate($date) {

$parts = explode('/',$date);

return "$parts[2]-$parts[1]-$parts[0]";

}

$_POST['date'] = mysqlDate($_POST['date']);

1 reply

David_Powers
David_Powers
Inspiring
August 16, 2009

I'm guessing this is due to '04/08/2009', being viewed as a string, not a date.

No, a date is inserted into MySQL as a string, so that's not your problem. MySQL stores dates in one format only: the ISO-recommended format YYYY-MM-DD. You need to reformat the date as 2009-08-04 (assuming you mean 4 August),

A
Anonymous
August 16, 2009

Hi David,

I thought as much.

I can reformat my calendar picker to become: yyyy-mm-dd, which gets accepted by MySQL.

Is there a way of keeping the calendar picker as dd-mm-yyyy, so it looks correct when people are entering the dates, then converting it, either through PHP or a MySQL statement, which enters it in the database as yyyy-mm-dd?

Thanks for your help

jon8
jon8Correct answer
Inspiring
August 17, 2009

Before the date gets inserted/updated, use this function:

function mysqlDate($date) {

$parts = explode('/',$date);

return "$parts[2]-$parts[1]-$parts[0]";

}

$_POST['date'] = mysqlDate($_POST['date']);

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices