Subject: Re: inserting date into mysql 2007-10-31 - By Dodger
Back
UPDATE tableA
SET sdate = '2007-1-1'
WHERE c=1
Reformat your date to the ones MySQL accepts.
"Although MySQL tries to interpret values in several formats, dates
always must be given in year-month-day order (for example,
'98-09-04'), rather than in the month-day-year or day-month-year
orders commonly used elsewhere (for example, '09-04-98', '04-09-98')."
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html
If you have screwy American style dates and need to reformat them
first, do that in your code...
my $dt = '1/1/2007';
my $q = <<"EOF";
UPDATE tableA
SET sdate = ?
WHERE c=1
EOF
my $st = $dbh->prepare($q);
$st->execute(join '-', (split /\//, $dt)[2,0,1]);
Generally though, storing dates in the format of descending order of
temporal magnitude is a good idea and has reasons it is. Namely
because:
YYYY-MM-DD hh:mm:ss
is readily sortable as a number simply by stripping out any non-digits to make:
YYYYMMDDhhmmss
Whereas something like hh:mm:ss, MM/DD/YYYY is not.
Consider two dates: the 19th of October, 1972 and the 3oth of April, this year:
19721019000000 < 20070430000000 <-- TRUE
Try 'em the other way and you get:
00000004302007 < 00000010191972 <--Numerically true but false datewise
So why make it harder?
BTW of course, if SELECT COUNT(*) WHERE c=1 returns 0, it won't do
anything anyway.
(BTW, Baron Schwartz... WTH was with that answer you gave? Didn't you
know what the poster meant? Whether they did an UPDATE or an INSERT
that date would still do the wrong thing, and while it may not have
been an INSERT statement, technically, if the date was not already
stored in the table, the data would have been inserted into it by an
update -- little 'i' insert -- in the sense that it wasn't in it
before and now it is. That's like someone saying 'I can't comb my
hair' and demonstrating with a brush, and telling them it's because
they're not using a comb when the real reason is they have a bad
uberfrizzy perm or they're bald or something.)
--
Dodger
On 31/10/2007, Kiran Annaiah <anna3144@(protected)> wrote:
> HI,
>
> I am having trouble inserting date into my table (Mysql)
>
> my sql statement looks like this...
>
> Example:
> update tableA set sdate='1/1/2007' where c=1;
>
> That doesnt do anything. I see there are diff formats in mysql for date. But
i didnt see any for entering date as mm/dd/yyyy
> Any tips and suggestions would be of great help
>
> thank you all:)
> Kiran
>
> __ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ __
> Boo!Scare away worms, viruses and so much more! Try Windows Live OneCare!
> http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl
_hotmailnews
--
Dodger
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=mysql@(protected)
|
|
