Crystal Reports Command Parameter Conversion to Date Issue (Where Clause)

[Former Member]

Hi,

I've created a CR with a command that has SQL hitting SQL Server. Start and End date parameters/prompts were also created.

So my command looks basically like this...

Declare @BeginDate AS VARCHAR(100)
Set @BeginDate = '{?BeginDate}'
Declare @EndDate AS VARCHAR(100)
Set @EndDate = '{?EndDate}'
--some manipulation is done on @BeginDate and @EndDate
Eventually @FinalBeginDate and @FinalEndDAte are set to date values via...
set @FinalBeginDate = CAST(@BeginDate as DATE)
set @FinalEndDate = CAST(@EndDate as DATE)

Then a SELECT is run returning many fields include PERIOD which is a VARCHAR(6) defined field containing year and month values in the format YYYYMM.

I want my WHERE clause to have something like this...

SELECT...WHERE CAST(PERIOD + '10' AS DATE) >= @FinalBeginDate and CAST(PERIOD + '10' AS DATE) <= @FinalEndDate

I consistently get this error...

Failed to retrieve data from the database.

Details 22007:[Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from charactrer string. [Database Vendor Code: 241]

NOTE: If I leave out the WHERE condition I'm trying to create and display @FinalBeginDate and @FinalEndDate the output is 12/1/2019 and 12/31/2019. And if I display the PERIOD it is 201912.

I have attempted tons of variations of this line:

CAST(PERIOD + '10' AS DATE) >= @FinalBeginDate and CAST(PERIOD + '10' AS DATE) <= @FinalEndDate

using CONVERT, CAST, etc. on PERIOD, @FinalBeginDate and @FinalEndDate but I can't get around the error.

Anyone have any suggestions?

Thanks

[DellSC]

Hi Michael,

Is there any place where PERIOD is null? If so, that might be causing your error and you'll have to account for that.

Also, I might look at this the other way around. Instead of trying to cast the PERIOD as a date, I might reformat the final begin and end dates as strings in YYYYMM format. It would look something like this:

IsNull(PERIOD, '000000') >= left(Convert(varchar, @FinalBeginDate, 112), 6) and 
IsNull(PERIOD, '000000') <= left(Convert(varchar, @FinalEndDate, 112), 6)

-Dell