MS SQL

MS SQL RSS

SQL date problem

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Sep 2006

Posts: 20

Reputation: saurabh_kanwar is an unknown quantity at this point

Solved Threads: 0

saurabh_kanwar saurabh_kanwar is offline

Offline

Newbie Poster

SQL date problem

Sep 20th, 2006

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.

•

Join Date: Oct 2005

Posts: 521

Reputation: pty is on a distinguished road

Solved Threads: 37

pty

Offline

Posting Pro

Re: SQL date problem

Sep 20th, 2006

•

Originally Posted by 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.

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.

Note to self... pocket cup

•

Join Date: Sep 2006

Posts: 20

Reputation: saurabh_kanwar is an unknown quantity at this point

Solved Threads: 0

saurabh_kanwar saurabh_kanwar is offline

Offline

Newbie Poster

Re: SQL date problem

Sep 20th, 2006

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