Re: Daylight Saving Code

nope. tried that first. Why is his way slower?

let's say he makes it a function so one can do:(I'm making it a function so I don't type the whole thing, but you don't have to make it a function, you can type the darn thing)
update mytable set mydate=convert_to_daylight(mydate) where ...

For every row it is going to have to evaluate to_date something like 15 or 16 times. In my case once he calls the package to_date is evaluated (to set up the constants) 15 or 16 times total.

So 15 times * # of rows is going to be slower than 15 times per session that calls the package. I took an update from 45 minutes to 2 doing this very technique. (thanks to the asktom site. Thanks Tom Kyte)

Jim

-- 
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Alex Filonov" <afilonov_at_yahoo.com> wrote in message
news:336da121.0306120643.7cbcd533_at_posting.google.com...


> "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message

news:<fgUFa.1206020$F1.142726_at_sccrnsc04>...


> > I've done this before and you are better off using a package. (I am not

sure


> > your code works, there were some dates during the late 70's where the

> > daylight savings was something like January and Feb.)  Also in a package

you


> > can declare some constants and thus if you are using it in an update

> > statement it will be much more efficient.

> > (the constants are private to the package body and thus initialized once

per


> > session when the package is first used - otherwise you are calling

to_date


> > to_char a zillion times per row in an update statement. (very

inefficient)


> > Jim

> >

>

> Nah. Oracle has operations with dual hardcoded in the server, so they

> are incredibly fast. Package would be slower and use more resources

> (those global variables are allocated somehow).

>

> > -- 

> > Replace part of the email address: kennedy-down_with_spammers_at_attbi.com

> > with family.  Remove the negative part, keep the minus sign.  You can

figure


> > it out.

> > "Aruneesh Salhotra" <asalhotra_at_customink.com> wrote in message

> > news:a29eb1e7.0306110843.5c979d6e_at_posting.google.com...

> > > I created a oracle query which detects the start and end date for

> > > dayight saving. I eventually used this, and passed the current year so

> > > to detect start/end dates for daylight saving for each year.

> > >

> > > I wanted to share the same with everyone.

> > >

> > > select SYSDATE x, 0 r1 from dual where SYSDATE between (select

> > > decode(to_char(to_date('04/01/2003','MM/DD/YYYY'),'D'),1,

> > > to_date('04/01/2003','MM/DD/YYYY')+7, 2,

> > > to_date('04/01/2003','MM/DD/YYYY')+6, 3,

> > > to_date('04/01/2003','MM/DD/YYYY')+5, 4,

> > > to_date('04/01/2003','MM/DD/YYYY')+4, 5,

> > > to_date('04/01/2003','MM/DD/YYYY')+3, 6,

> > > to_date('04/01/2003','MM/DD/YYYY')+2,

> > > to_date('04/01/2003','MM/DD/YYYY'))  sday1 from dual) and (select

> > > decode(to_char(to_date('09/01/2003','MM/DD/YYYY'),'D'),1,

> > > to_date('09/01/2003','MM/DD/YYYY')+7, 2,

> > > to_date('09/01/2003','MM/DD/YYYY')+6, 3,

> > > to_date('09/01/2003','MM/DD/YYYY')+5, 4,

> > > to_date('09/01/2003','MM/DD/YYYY')+4, 5,

> > > to_date('09/01/2003','MM/DD/YYYY')+3, 6,

> > > to_date('09/01/2003','MM/DD/YYYY')+2,

> > > to_date('09/01/2003','MM/DD/YYYY'))  sday2 from dual);