Re: Calculating a field for a date range

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.
Received on Fri Sep 01 2000 - 17:12:02 CDT