cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Command, filter data within date range.

Former Member
0 Kudos

Hi Everyone,

For some reason, this command's where statement works fine in DBVisualizer, but when I use it in Crystal, with the date range criteria, the report doesn't return any data.

Any ideas?

Full SQL Statement

SELECT DISTINCT BI_CLOSE_DT, BI_SO_COM, BI_SO_NBR, BI_ACCT, BI_OPEN_DT, BI_SO_DET_KEY, BI_SO_STAT_CD, BI_SO_TO_ACCT, BI_SO_TO_CUST_NBR, BI_SO_TYPE_CD, BI_SRV_LOC_NBR
FROM BI_SO_DET
WHERE BI_SO_STAT_CD = 'X' and BI_SO_TYPE_CD IN ( 'NEW', 'NCBM', 'NEW-WF' ) and BI_SO_TO_ACCT IS NOT NULL and (BI_OPEN_DT  >= to_date('1-jan-2005') and  BI_OPEN_DT < to_date('31-dec-2005'))

Edited by: Johnbr on Apr 1, 2011 5:45 PM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi John,

What is the datasource you are using ? If you are using ODBC, try with a Native driver, because some times it can't translate your date fromat to crystal date format. Crystal can't understand few date formats.

or

Remvoe date range from your where clause and create a add command and create two parameters like from date and to date and use these parameters in your add command to filter the data.

Thanks,

Sastry

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Sastry,

It's Oracle 11g via ODBC. It's a date time field DD/MM/YYYY HH:MM:SSAM

All I wanted to do was restrict the amount of data the query returned for some testing and further development of the report (return a dozen records instead of 17000). I was able to do this by using a simple extract statement instead:

and EXTRACT(YEAR FROM TO_DATE(BI_OPEN_DT)) > 2010

John

Former Member
0 Kudos

Hi John,

If you create a parameter in Add Command, it will not pull all the records into your report and filter, this will filter all the records at database end and give you the filtered data.

Thanks,

Sastry

Former Member
0 Kudos

Try changing your date format to '2005-01-01' & '2005-12-31'.