Bear with me cos I'm learning!

I have an issue with dates being converted to all zeroes. I have a datetime field in one of my tables which should be populated by a date coming in from a web app. The date/time coming in is in the format 7/17/2010 9:18:18 (for example). I assume it's because MySQL was expecting the date in a certain format, and I'm giving it the wrong one. I tried changing the format of the datetime_format variable, but it doesn't help. Google is throwing up some confusing results!

Any idea wise ones? 🙂

MySQL online documentation is really good. It helped me loads during my brief flirtation. I think I had the same problem in fact. Have you looked here?

Also, is your MySQL field a datetime rather than a date?

I'm a novice too, however if your getting php to write to the database and the date entered is the same date you enter it i.e. is current as opposed to a date in history. If for the date field on the Sql string instead of the variable use 'NOW()' and it'll fill the database with the correct date/time at the time you add it.. if that makes any sense at all (again I'm also a complete novice).

it expects yyyy-mm-dd hh:mi:ss

It is a datetime field.

Steve – I am using PHP. It's a time stamped transaction that's coming in, so I need the actual time rather than current time. I have a timestamp field that is being correctly populated (after I tweaked the format) thus:
$systemtime = date('Y/m/d h:i:s', $currenttime);

But that takes UNIX time and converts it accordingly.

simon – I've tried changing what it's expecting (I think!), and setting "ALLOW_INVALID_DATES" but no change. Can I reformat the date string I'm getting?

Don't you want:-

$systemtime = date('Y-m-d h:i:s', $currenttime);

(note hyphens rather than slashes)

That bit works allthepies so I'm leaving it!

I've just applied the same logic and converted my incoming time to UNIX time and back again, I now get the date populated in my DB.

$newphoneTime = strtotime($phoneTime);
$phoneTime = date('Y/m/d h:i:s', $newphoneTime);

First off, check to make sure that you're actually being passed values. 9/10 it turns out that you're not getting values from the source system. Check this by changing the data type of your destination field to varchar(255). It could be that you're recieving values that are yyyy-mm-dd hh.mm.ss.00 so the length of the field could be causing issues. Also try casting the field as a datatime before delivery. define the format before table insertion just to make sure.

Cheers flange – I did that early on to make sure I was getting something (I was). I can see the transaction coming in on the web server logs, and I passed the raw data through the php urldecode function to see what the date looks like. Fixed now I think, though something might be a bit squiffy as the time says 01:.. instead of 13:…

>as the time says 01:.. instead of 13:…

That's because the lower case 'h' in the format string is a 12-hour format, change it to an upper case H and you'll get 24-hr.

Aye, just twigged that!

Cheers 🙂

… and don't forget timezones and daylight saving 🙂