AnsweredAssumed Answered

NSQL start_date Parameter Issue

Question asked by Sybren_Steen on Sep 29, 2015
Latest reply on Sep 29, 2015 by 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.

Outcomes