|
|
Posted by Lars Eighner on 06/21/07 19:31
In our last episode,
<mv7l73l0pdv6bo8mnq1kqrv6djubcjnoon@4ax.com>,
the lovely and talented Michael Fesser
broadcast on comp.lang.php:
> .oO(mantrid)
>>Is it best to do that in Excel or use a php date format function?
>>In Excel it is dd/mm/yyy
>>but it wont allow me to save as a CVS file in the form yyy-mm-dd hh:mm:ss,
>>it just changes it back.
>>ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the sql
>>statement but its not working, am I using this function wrongly?
> Yep, but doesn't matter in this case.
>>Do you know a php function that does a similar thing? I know the date format
>>characters (eg d M Y etc) but can only see examples of their use with
>>date(), which is the current date, not a date from my CVS file.
> Try strtotime(). If it doesn't work as expected (for example dd/mm/yyyy
> vs. mm/dd/yyyy), use string functions to split the Excel date and create
> your own date string in MySQL format.
Some additional notes: You used yyy twice in your post, so I wonder
if it is a typo. MySQL will accept 2 or 4 digit years, but I have my
doubts about 3.
Also DATETIME and DATE field will accept any separator, so if something goes
screwy with the date and you send 'HH:MM:SS' first it will try to interpret
that as the date HH-MM-SS. For MM over 12 and SS over 31 that will always
produce an invalid date which will default to 0000-00-0000.
I believe TIMESTAMP is stricter. Also, I note there have been changes
in the relevant functions / data structures of both PHP and My SQL, so
you should check the manuals of the version you are actually running.
Perfectly good advice may not work for you if you have a legacy system.
But if you discover exactly what your MySQL wants, rolling your own function
to manipulate the date string is good advice that will always work. If you
have a MySQL client on the pertinent server and a sandbox database, the best
way to determine exactly what your MySQL wants is to try it from the MySQL
client. You cannot expect to use PHP to write a query line that works if
you cannot type in a query that works at the MySQL client prompt.
--
Lars Eighner <http://larseighner.com/> <http://myspace.com/larseighner>
Countdown: 578 days to go.
Owing to googlegroups not screening users to eliminate spammers and other
USENET abusers, I do not see most posts from googlegroups.
Navigation:
[Reply to this message]
|