SQL date problem

[saurabh_kanwar]

Hi Everyone,

I'm stuck in a bit of a problem. I've got a table called sales with a date field in it, but the date is not in the date format, its in the number format and it's too late to change it as the table's got over 70,000 records in it and I also don't have administrative right to change the date. So the date comes up as 20000025. I've got records from 1999 to present date 2006. The problem I'm having is that I need to group the data according to year corresponding to Japanese calendar (example 1/04/05 to 31/0306).
I've already spent a lot of time but can't find any solution. I'm using access 2000 file format. Please help.

[pty]

Hi Everyone,

I'm stuck in a bit of a problem. I've got a table called sales with a date field in it, but the date is not in the date format, its in the number format and it's too late to change it as the table's got over 70,000 records in it and I also don't have administrative right to change the date. So the date comes up as 20000025. I've got records from 1999 to present date 2006. The problem I'm having is that I need to group the data according to year corresponding to Japanese calendar (example 1/04/05 to 31/0306).
I've already spent a lot of time but can't find any solution. I'm using access 2000 file format. Please help.

I don't know much about access (except to avoid it).

It was SQL I'd suggest you look at the convert() function in tsql.

I don't really get what you mean with your data conversion either but you can use cast() to change the format to Japanese.

By not changing the bad column you are making more work for yourself in the long run.

[saurabh_kanwar]

Hi

Thanks For your reply. I've already converted the number into date format using following code

CDate(Left([20060920],4) & " " & Mid([20060920],5,2))

What I actually want is to group the data by year. But the problem is that according to Japanese calendar the year starts from 1st April and goes till 31st of March. So I have to sort the data according to that time interval.
I wish I could change the column but the main database as at another location and I'm just accessing it by some sort of link.

Thanks For your time