inserting date into mysql

--_ee75c597-b328-4572-b0bb-dc1d993006ea_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

HI,

I am having trouble inserting date into my table (Mysql)

my sql statement looks like this...
=20
Example:
update tableA set sdate=3D'1/1/2007' where c=3D1;

That doesnt do anything. I see there are diff formats in mysql for date. Bu=
t 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!=A0Scare away worms, viruses and so much more! Try Windows Live OneCare=
!
http://onecare.live.com/standard/en-us/purchase/trial.aspx?s _cid=3Dwl_hotma=
ilnews=

--_ee75c597-b328-4572-b0bb-dc1d993006ea_--

Re: inserting date into mysql

Hi,

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

That is an UPDATE statement. You need to use INSERT to insert data.

Baron

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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.h tml

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 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=gcdmp-msql-mysql-modules@m .gmane.org

RE: inserting date into mysql

--_672e41e1-2435-4dec-9991-ef94c344664b_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

Thank you for the detailed explanation.
I did look at the mysql date formats, but since i had some of my dates alre=
ady in a mm/dd/yyyy format i wanted to keep it the same way.
=20
But i see the advantages of having it in the other format(yyyy-mm-dd). I wi=
ll probably change all my dates into that.
=20
=20
Thanks
K



> Date: Wed, 31 Oct 2007 17:37:10 -0700> From: el.dodgero@gmail.com> To: an=
na3144@hotmail.com> Subject: Re: inserting date into mysql> CC: perl@lists.=
mysql.com> > UPDATE tableA> SET sdate =3D '2007-1-1'> WHERE c=3D1> > Reform=
at 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 o=
rder (for example,> '98-09-04'), rather than in the month-day-year or day-m=
onth-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.h tml> > =
If you have screwy American style dates and need to reformat them> first, d=
o that in your code...> > my $dt =3D '1/1/2007';> my $q =3D <<"EOF";> UPDAT=
E tableA> SET sdate =3D ?> WHERE c=3D1> EOF> my $st =3D $dbh->prepare($q);>=
$st->execute(join '-', (split /\//, $dt)[2,0,1]);> > Generally though, sto=
ring dates in the format of descending order of> temporal magnitude is a go=
od 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 an=
d you get:> 00000004302007 < 00000010191972 <--Numerically true but false d=
atewise> > So why make it harder?> > BTW of course, if SELECT COUNT(*) WHER=
E c=3D1 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 wron=
g 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 co=
mb 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> uberfriz=
zy perm or they're bald or something.)> > -- > Dodger> > On 31/10/2007, Kir=
an Annaiah wrote:> > HI,> >> > I am having trouble i=
nserting date into my table (Mysql)> >> > my sql statement looks like this.=
...> >> > Example:> > update tableA set sdate=3D'1/1/2007' where c=3D1;> >> =
> 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 suggest=
ions would be of great help> >> > thank you all:)> > Kiran> >> > __________=
_______________________________________________________> > Boo!Scare away w=
orms, viruses and so much more! Try Windows Live OneCare!> > http://onecare=
..live.com/standard/en-us/purchase/trial.aspx?s_cid=3Dwl_hot mailnews> > > --=
> Dodger
____________________________________________________________ _____
Windows Live Hotmail and Microsoft Office Outlook =96 together at last. =A0=
Get it now.
http://office.microsoft.com/en-us/outlook/HA102225181033.asp x?pid=3DCL10062=
6971033=

--_672e41e1-2435-4dec-9991-ef94c344664b_--