Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Calculating a field for a date range

Re: Calculating a field for a date range

From: <sean_semone_at_my-deja.com>
Date: Sat, 02 Sep 2000 16:15:34 GMT
Message-ID: <8or92r$p0p$1@nnrp1.deja.com>

Hey Pete--

Thanks for the quick reply. I tried these out, but didn't have any luck. In the Access code I can't figure out how the select statement determines when the Academic Year begins and ends. Should there be something in there that I tell it all days between July 1 and June 30 are the Academic Year of that year?

Thanks again!

Cheers,

Sean

In article <SNVr5.7647$f65.366909_at_news-west.usenetserver.com>,   "Pete" <pachavez_at_excite.com> wrote:
> I answered this from work but it didn't get through. Here is the
 solution in
> Access and Oracle syntax.
>
> Access:
> select CStr(Year(DateAdd("m",-6,[RECEIVED_DATE]))) &
> CStr(Year(DateAdd("m",-6,[RECEIVED_DATE]))+1) AS AY from [yourTable];
>
> Oracle
> select to_char(round(RECEIVED_DATE,'YYYY'),'YYYY') ||
> to_char(round(add_months(RECEIVED_DATE,12),'YYYY'),'YYYY') from
 yourTable;
>
> I haven't tested either but it should work with little or no
 alterations.
>
> Pete
> <sean_semone_at_my-deja.com> wrote in message
> news:8oorlk$4sj$1_at_nnrp1.deja.com...
> > I need to do the following:
> >
> > I have a date field and need to group on the date field based on a
> > range of dates. For instance all dates between 7/1/1998 and
 6/30/1999
> > need to be calculated as the string 19981999.
> >
> > Right now I am doing this in Access looking at data on an Oracle 8
> > Database with the following calculated field:
> >
> > AY: IIf([RECEIVED_DATE] Between #7/1/1989# And #6/30/1990#,19891990,
> > ([RECEIVED_DATE] Between #7/1/1990# And #6/30/1991#,19801981, ....
> >
> > okay so you get the idea. This works great for 5 or ten years of
> > ranges, but it would be nice if I could do something like the
 following
> > pseudo-code:
> >
> >
> > IIf([RECEIVED_DATE] Between #7/1/YYYY# And #6/30/YYYY#,(CONCATENATE
> > ([RECEIVED_DATE #YYYY#]),[RECEIVED_DATE #YYYY#+1])),"n/a")
> >
> > Of course that doesn't work but it is 'lgically' what I would like
 to
> > do.
> >
> > There is also a "Round" (see table 3-11 in the oracle 8 SQL command
> > reference) function that can be used with dates and times in Oracle
 but
> > I couldn't get that to work, although it looks like it might do the
> > trick if I could round the year from the date in the file up (on
> > 7/1/YYYY and then concatenate that result with the previous year.
> >
> > Anyway...that is where I am..I'm a moving from SQL Server to Oracle
 so
> > any other links/texts you can point me to would be great.
> >
> > Thanks,
> >
> > Sean
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Sep 02 2000 - 11:15:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US