How to filter a range of dates?

[adrianzazugmail.com]

Hi, I'm new to this.

My project needs filtering by dates, an example:

ROPORT QUERY:

SELECT fields
FROM table_name
WHERE
name_field_date = 18/09/2017

END
where name_field_date is a field type date
The problem is that it tells (Ireport) me that there are no records, try changing the date format to 09/18/2017 and it does not give results, change the format to 2017-09-18 and there are no records, but if I write
name_field_date > = 18/09/2017 gives me records for 2016.
I do not understand how the Ireport date takes.
Thank you.

[hozawa]

Just use the $P!{} parameter to put your sql date condition.

[adrianzazugmail.com]

Thanks for answering, I do and post as I did.

[adrianzazugmail.com]

Another question I have is: in my project I am handling a database by ODBC connection, in which a field is date type of 10 characters, when I see my database the form in this way is dd / mm / yyyy.
in my query I have more or memos like this:
SELECT *
FROM 'table_name' table_name // (this part I understand it)
WHERE field_date= 18/09/2017
and tells me page without results.
chage to:
WHERE field_date = $ P! {Date}
creating a "date" parameter with parameter class java.util.DATE
and gives me the following error:

Cause of: java.sql.SQLException: [Microsoft] [ODBC Driver dBASE] Syntax error (operator missing) in query expression 'date_field = Mon Sep 18 00:00:00 CDT 2017'.
Beforehand I thank you for the attention and I hope you can explain. Thank you.

[kaushiklokesh123]

Hi adrianzazu_1,

if you are using $P!{} then the parameter should be of string type.

If you want to run the report for date range, I suggest you use multiple parameters like WHERE field_date  between $P{Start_Date} and $P{End_Date} and both parametrs should of date type.

[adrianzazugmail.com]

Thanks, I'll do it and post the result

[adrianzazugmail.com]

Thanks, kaushiklokesh123 and hozawa, I succeeded and I understood what was wrong.
My query looks like this:
SELECT fields
FROM table_name
WHERE field_date LIKE '$ P! {Date} '// DATE is a java.lang.String parameter and change the equals (=) by LIKE, and add the apostrophes ( ' ' )
and it worked!!!!
The fields in the band details also change them but does the same as java.lang.String as java.util.Date. It works, thank you.

I also tested with BETWEEN thus leaving my query:
SELECT fields
FROM table_name
WHERE
      field_date BETWEEN $ P {Start_Date} AND $ P {End_Date}
Where $ P {Start_Date} and $ P {End_Date} are of type java.util.Date both.
I also work. Thank you.

Another question is: what is the difference between: $ P {} and $ P! {}, (exclamation mark). First of all, Thanks.