|
|
Posted by mantrid on 06/21/07 20:36
Hello
I used phpmyadmin to run an sql to insert a record into the table. The only
one that worked was
INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,price,stamp,comm,datetime)
VALUES ('',1, 2, 1, 11111, .76, 3.54, 7, '2002-04-10')
ie Using a date in the correct format as the mysql table and in quotes (hrs
mins secs were not needed), leave out the quotes or in any other format and
it doesnt work.
ian
"Lars Eighner" <usenet@larseighner.com> wrote in message
news:slrnf7lkh9.1qie.usenet@goodwill.larseighner.com...
> 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]
|