Re: inserting date into mysql

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 <[email 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/[email protected]