Date prompt not working

[mahgar]

I have a Start and End Date date prompt and the [Date of Hire] <= ?End Date? is not working.
If I select End Date = 31/01/2014 not report comes out. But when I select 01/02/2014 then the report shows.
Note: I already casted the dates.

Name      Date of Hire
Employee 1    31/01/2014
Employee 2   31/01/2014

[MFGF]

Hmmmm...

You're not using MM/DD/YYYY format by any chance?

MF.

[mahgar]

Hi MF,

No, all of our date fields are on TIMESTAMP format (2014-01-31 00:00:00) and weird this is the ">=" is working and the "<=" is not

Thanks

Mahgar

[chinnucognos]

i have tried with to_char(start_date,'MM/DD/YYYY') AS Date_of_HIre
then date_of_hire<= ?end_date?..
Have you tried this same....

[mahgar]

Hi Chinnucognos,

Here's the report that im expecting:

Detail Filter: [Date of Hire] >= ?pDateofHireFrom? and [Date of Hire] <= ?pDateofHireTo?

If ?pDateofHireFrom? >= 30/01/2014 ---(or 31/01/2014)
and ?pDateofHireTo? <= 31/01/2014 then the expected will show.

Expected Output:
Name             Date of Hire
Employee 1    31/01/2014
Employee 2    31/01/2014

But what happens is, if I select the prompts above there's no report will show.
The report will only show if I change the ?pDateofHireTo? to "01/02/2014" or change it to a day later.

[mahgar]

Hi Guys,

Does any of you guys experience the same scenario?

TIA!

[Lynn]

You mention in your first post that you already casted the dates, and then later you mention that all your date fields are timestamp data types. What exactly are you casting? Do you use a calendar control for your prompt? Is the time component in the database vs the time component of your prompt affect the result? I suspect the time component and/or incorrect casting are to blame.

As a side note, I'd suggest that between logic is a little cleaner to read since your range is inclusive:

Code Select Expand


[Date of Hire] between ?pDateofHireFrom? and ?pDateofHireTo?


[Francis aka khayman]

explicitly format your dates just as MFGF implied.

reason why 31/01/2014 is not working is because it might be an invalid month
01/02/2014 however can be Jan 2, 2014 or Feb 1, 2014 which is always a valid month.

[mahgar]

Hi chinnucognos,

I tried your solution as well but it doesn't works fine.


What I did was adding the expression below. The "<=" function doesn't treat my "TO" prompt as timestamp.
It works fine finally.
[Date of Hire] >= ?pDateofHireFrom?
and
[Date of Hire] <= _add_days(cast((?pDateofHireTo?) as TIMESTAMP),1)