varchar to datetime...out-of-range

[partymong]

Hi All,
I have the query below which doesn't seem to work, I get the following message:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


NOTE:- The "creation date" is a varchar field....

I have tried using cast with the same problem...

Any Ideas?? Thanks in advance for your help

P


/* Declare the variable */
Declare @NumCR as int
Declare @Open as int
Declare @Closed as int
/* Add more months here to make a rolling 12 */
Declare @Month1 as datetime
Declare @Month1T as char(15)
Declare @Month2 as datetime
Declare @Month2T as char(15)
Declare @Month3 as datetime
Declare @Month3T as char(15)

/* set the rolling 12 month dates here */
select @Month1 = '01/01/2004'
select @Month1T = 'January'
select @Month2 = '01/02/2004'
select @Month2T = 'February'
select @Month3 = '01/03/2004'
select @Month3T = 'March'

/* Now summary information has been set, gather and insert the TOTAL metrics */
Select @NumCR = count(*) from "ALL_CRs_MW" where ("Programme" = 1 OR "Programme" = 2) AND "Creation Date" > @Month1 AND "Creation Date" < @Month2
Select @Open = count(*) from "ALL_CRs_MW" where (("Programme" = 1 OR "Programme" = 2) AND "CRStatus" = 1) AND "Creation Date" > @Month1 AND "Creation Date" < @Month2
Select @Closed = count(*) from "ALL_CRs_MW" where (("Programme" = 1 OR "Programme" = 2) AND "CRStatus" = 0) AND "Creation Date" > @Month1 AND "Creation Date" < @Month2
insert Summary values ('Total CRs', @Month1T, @NumCR, @Open, @Closed)

 

[partymong]

Sorry All,

I've solved the problem...

The "creation date" field contained 'rogue' values. Which I identified using...

SELECT * FROM "ALL_CRs_MW" WHERE ISDATE("Creation Date") = 0

P