Calculating a field for a date range

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 - 13:14:31 CDT