date query when date field is char and mmyy format

Hi All,
I have a field which stored date as char datatype in mmyy format.
Acutally, I have inherited this and there is no way I can change the
design of the database.
Now, I needed to run a qeury between two months (from December 2005 to
January 2006).
My query was:
select * from table1 where claim_date between '1205' and 0106'
This did not give me any records whereas, I know there are records for
the month of December (claim_date : 1205)
I understand that 0106 is smaller than 1205 and that is the main reason
that I am not getting any records. Because if I change it to
select * from table1 where claim_date between '0106' and '1205', then
it gives me the results I want. I feel this is not right.
My question is how can I handle this problem in the query, so that I
can really use the date range as claim_date between '1205' and '0106'.
CREATE TABLE [dbo].[table1] (
[Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Insert table1
select 'D124', '1205', 'A'
union all
select 'D243', '0106', 'A'
Thanks a million in advance.
Best regards,
MamunWithout changing the table structure and storing the right data in the right
format, don't expect this to have sizzling performance.
SELECT * FROM
(
SELECT
*,
claim_date_calc = '20'+RIGHT(claim_date,2)+LEFT(claim_date
,2)+'01'
FROM
table1
WHERE
ISDATE('20'+RIGHT(claim_date,2)+LEFT(cla
im_date,2)+'01') = 1
)
x
WHERE CONVERT(SMALLDATETIME, claim_date_calc) BETWEEN '20051201' AND
'20060101';
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1139340637.499729.6330@.f14g2000cwb.googlegroups.com...
> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>|||where substring(Claim_Date,3,2)+substring(Clai
m_Date,1,2)
between '0512' and '0601'|||Both solutions worked great. I was not looking at the performance but
to get the results.
Thanks a ton to both of you.
best regards,
mamun|||Another approach would be to convert the values for claim_date into honest
datetimes, and compare to other datetime values
SELECT Claim_Number, Claim_Date, Prime_ABC
FROM table1
WHERE
CONVERT(datetime, RIGHT(Claim_Date,2) + LEFT(Claim_Date,2) + '01', 12)
BETWEEN '20051201' AND '20060101'
With all respect to Alexander, using BETWEEN with character datatypes gives
me the heebee jeebees.
"microsoft.public.dotnet.languages.vb" wrote:

> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>