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

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

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  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=lists@pantek.com

Do you need more help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek