[Closed] Any MySQL geeks around? 🙂

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 [url= http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format ]here?[/url]

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 🙂