NSQL start_date Parameter Issue

[Sybren_Steen]

Hi all,

 

I am trying to grab results from the table PRTIMEPERIOD based on the input of the user, but it does not work.

An direct SQL statement on the db gives results. I guess that the date picker input from the user is not compatible with the date type in the db?

 

Original

SELECT @SELECT:TP.PRID:PRID@,

@SELECT:TP.PRUID:UNIQUE_CODE@,

@SELECT:TP.PRISOPEN:PRISOPEN@,

@SELECT:TP.PRFINISH:PRFINISH@,

@SELECT:TP.PRSTART:PRSTART@,

@SELECT:TO_CHAR(TP.PRSTART,'DD/MM/YYYY') || ' - ' || TO_CHAR(TP.PRFINISH-1,'DD/MM/YYYY'):TIMEPERIOD@

FROM   PRTIMEPERIOD TP

WHERE  TP.PRISOPEN <> 3

and    (@WHERE:PARAM:USER_DEF:DATE:START_DATE@ IS NULL OR TP.PRSTART >=

    @WHERE:PARAM:USER_DEF:DATE:START_DATE@) AND

(@WHERE:PARAM:USER_DEF:DATE:END_DATE@ IS NULL OR TP.PRSTART <=

    @WHERE:PARAM:USER_DEF:DATE:END_DATE@)

AND    @FILTER@

Modification

SELECT @SELECT:TP.PRID:PRID@,

@SELECT:TP.PRUID:UNIQUE_CODE@,

@SELECT:TP.PRISOPEN:PRISOPEN@,

@SELECT:TP.PRFINISH:PRFINISH@,

@SELECT:TP.PRSTART:PRSTART@,

@SELECT:TO_CHAR(TP.PRSTART,'DD/MM/YYYY') || ' - ' || TO_CHAR(TP.PRFINISH-1,'DD/MM/YYYY'):TIMEPERIOD@

FROM   PRTIMEPERIOD TP

WHERE  TP.PRISOPEN <> 3

and    (TP.PRSTART BETWEEN TO_CHAR (@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'DD/MM/YYYY')

AND TO_CHAR (@WHERE:PARAM:USER_DEF:DATE:END_DATE@, 'DD/MM/YYYY'))

AND    @FILTER@

 

It's about the bold part. The user gives a start date and end date. I have tried different things but all with no results.

I also tried:

WHERE TP.PRSTART BETWEEN @WHERE:PARAM:USER_DEF:DATE:START_DATE@ AND 
@WHERE:PARAM:USER_DEF:DATE:END_DATE@

This is also not working:

WHERE TP.PRSTART > @WHERE:PARAM:USER_DEF:DATE:START_DATE@

I'll guess that the user input, a date picker, is not compatible with the date field in the database?

When I execute the query directly on the db then I get results.

 

Any help is much appreciated, thank you in advance.

[navzjoshi00]

TP.PRSTART BETWEEN TO_CHAR (@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'DD/MM/YYYY')

AND TO_CHAR (@WHERE:PARAM:USER_DEF:DATE:END_DATE@, 'DD/MM/YYYY'))

 

The above should be

 

TP.PRSTART BETWEEN TO_DATE(@WHERE:PARAM:USER_DEF:DATE:START_DATE@, 'DD/MM/YYYY')

AND TO_DATE(@WHERE:PARAM:USER_DEF:DATE:END_DATE@, 'DD/MM/YYYY'))

Check if this helps

 

NJ

[Sybren_Steen]

Thanks NJ, but I have tried it to change it to to_date but unfortunately it doesn't work.

I also tried:

WHERE TP.PRSTART > @WHERE:PARAM:USER_DEF:DATE:START_DATE@

Somehow it doesn't take the user defined start date correctly, because when I type the query directly then it works:

WHERE TP.PRSTART > '31-8-2015' (this gives results)

[Andrew LERNER]

Why not drop the parameters, and just set the PRSTART Field Element Type to "Date Range"? Isn't that what you want to accomplish?

[Sybren_Steen]

Thanks Andrew but this is a portlet which will be used by all users. I find the date picker more user-friendly. I think that the date range results in more questions.

[David Morton]

I don't think you have understood the advice you were given ; a "Date Range" filter parameter would work exactly like what it appears you are trying to accomplish with two user-parameter date fields. Just add PRSTART coulm to the filter section of your portlet and change its display type (in the List Filter Section / Fields) - to "Date Range" (from "Date")

[Sybren_Steen]

I understand it now. Thanks for the clarification. I have used PRSTART as colum in the filter section and that works perfect!

 

Thank you for the help all.