Subject: RE: inserting date into mysql 2007-11-01 - By Kiran Annaiah
Back
Thank you for the detailed explanation.
I did look at the mysql date formats, but since i had some of my dates already
in a mm/dd/yyyy format i wanted to keep it the same way.
But i see the advantages of having it in the other format(yyyy-mm-dd). I will
probably change all my dates into that.
Thanks
K
> Date: Wed, 31 Oct 2007 17:37:10 -0700> From: el.dodgero@(protected)> To:
anna3144@(protected)> Subject: Re: inserting date into mysql> CC: perl@(protected)
.mysql.com> > 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)
.com> 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
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ __
Windows Live Hotmail and Microsoft Office Outlook ? together at last. ?Get it
now.
http://office.microsoft.com/en-us/outlook/HA102225181033.aspx?pid=CL100626971033
|
|
