Dates from form into MYSQL getting 1969-12-31 in MYSQL

PHP
1

Help I am using these parameters and cannot solve the issue of wrong date in the database:

$DateAA=date(‘Y/m/d’, $_POST[‘DateAA’]);
$DateBB=date(‘Y/m/d’, $_POST[‘DateBB’]);

$query = sprintf ( “insert into places ( DateAA, DateBB )values ( ‘%s’, ‘%s’ ) )”, mysql_real_escape_string($DateAA),
mysql_real_escape_string($DateBB) );

I have to use the sprintf since this string is actually quit larger with many other string variables. etc… but all I get is the incorrect date of 1969-12-31 and 1969-12-31 respectfully in MYSQL. The date was gotten from a form variable where DATEAA and DATEBB are assigned a date chosen by the user. In this case it was 1/10/2009 and 7/12/2012. Of course the date really doesnt matter I am just showing the format.

I have tried many things form this forum and every other but can’t get passed the error from string to a date in the database.

I tried this solution posted in this forum "$date = mysql_real_escape_string($_POST[‘date’]);
$date = implode(‘-’, array_reverse(explode(‘-’, $date))); " and many many others but all that occurs with this one is the the date in the database goes to 0000-00-00.

2

mysqldate format is yyyy-mm-dd. is your post date is in this format?

3

Have you tried storing the dates as timestamps? That always seems to work for me.

4

no it is in this format mm/dd/yyyy as follws when i put a loop on the _POST: DateAA: 7/18/2011
DateBB: 10/14/2012

5

I changed the MYSQL to timestamp and had the same result. :frowning: it became 0000-00-00 00:00:00 and

0000-00-00 00:00:00

6

Change your MySQL column types to INTEGER(10) rather than timestamp. You also have to convert your dates to timestamps:

$DateAA=strtotime($_POST[‘DateAA’]);
$DateBB=strtotime($_POST[‘DateBB’]);

7

OK COOL some progress now I get 1309759200 and 1354345200 which I can see is going to need to be converted back when I place it back on the screen. Let me go to the documents and see what I need to do to do that?

8

Thank YOU programthis this is solved I used the below code in my loop after i did what u said above and got exactly what I was wanting!

<td><?php echo date(‘m-d-Y’,$DateAA); ?></td>
<td><?php echo date(‘m-d-Y’,$DateBB); ?></td>

A solution in one hour and 25 minutes. Awesome.

9

Glad I could help!

In case you want to format the date the way it was entered by the user (7/12/2012), use date(‘n/j/Y’, $DateAA);
n = numeric month without leading 0
j = day of the month without leading 0

10

Dates are stored in the database by entering them in as CCYY-MM-DD - the internal format is different and you can have it formatted however you want when you extract it from the database. The only limitation is on what format it has to be in when you store it.

Never use any type of field other than date or timestamp to store a date as if you use anything else you lose most of the date processing functionality built into the database.

11

Well the problem set I ran into would not work any other way?

So what direction can you point me to make sure that I can store it in the DB as a date field since it is has gone from a string variable at the form level then converted to an integer in order to store it at the DB level? Now I would like it as a date variable in the DB so that way I can do against it at the server level. (Which is the very thing I want to do for the application I am writing.) Currently if I store it as a integer 10 I have to add an extra step when I run a database procedure against it to do the math required in the application. (Something I actually am seeing as a problem.)

Any direction of how I can store a string that is formatted as a date first then needs to be converted into a date so that it can be imported using a sprint with MYSQL would be greatly appreciated. I have tired almost every hybrid in every help I could find.

Thanks in advance.

12

If it is in any kind of date format to start with then the PHP date functions ought to be able to convert it to CCYY-MM-DD format.

Have you tried:

date(‘Y-m-d’, $_POST[‘DateAA’]);

13

Yes as follows:

Originally Posted by mikeypp
Help I am using these parameters and cannot solve the issue of wrong date in the database:

$DateAA=date(‘Y/m/d’, $_POST[‘DateAA’]);
$DateBB=date(‘Y/m/d’, $_POST[‘DateBB’]);

the / or - did not matter i ended with a date field in the database with 12/31/2011 date

14

The / would matter as it is required to be -

If using - doesn’t work that means that $_POST[‘DateAA’] doesn’t contain a valid timestamp that you are converting into a date. Your visitors are not likely to know how to enter a timestamp and are more likely to enter the date in some other format so most likely you need to convert $_POST[‘DateAA’] into a timestamp BEFORE using it in the date function in order to get the code to work

15

$DateAA=date(“Y-m-d”, strtotime($_POST[‘DateAA’]));
$DateBB=date(“Y-m-d”, strtotime($_POST[‘DateBB’]));

Solved it using the original DATE variable in MYQL. Thanks everyone who helped me with an intermediate solution which gave me some pice of mind and then pushed me into the right direction! :slight_smile: