date parameter in an SQL command

[mikiap]

Hi

I have created a report in CR9 based on a Command. The command looks like this:

SELECT A.SITE_NAME, A.CUSTOMER_ID, A.CUSTOMER_NAME, A.START_DATE, A.RECORD_FROM, A.OUTAGE_TYPE
FROM IML.TAMSSLA2 a
WHERE A.START_DATE>=date(year({?datum})-01-01) And A.START_DATE<={?datum}
The command has a parameter = datum
The command seems to work but the results are not the expected ones. If I input the date=11/12/2004 when I run the report it should display the data between 01/01/2004 and 11/12/2004 but in reality it takes also the data from the previous years. From here I conclude the formula
date(year({?datum})-01-01) is not a correct one. Any idea which is the right way to write it?

Thanks a lot
Miki

 

[mikiap]

I forgot to say that the report is using a DB2 database.
Thanks

 

[kskid]

Change the data formula to this

WHERE A.START_DATE>=date(year({?datum})-1,1,1) And A.START_DATE<={?datum}

Cheers,

-LW

 

[mikiap]

I tried already the sintax A.START_DATE>=date(year({?datum}),1,1) and it says "the number of the parameters for the functions is incorrect".

Miki

 

[Madawc]

Do separate formula fields for your start date and end date. Display them on the report. See what you actually get from your formulas - always the best way to correct them.

Madawc Williams (East Anglia, UK)