This discussion is archived
4 Replies Latest reply: Dec 10, 2012 11:14 PM by AlexAnd RSS

how to use the calendar in Publisher ???

950017 Newbie
Currently Being Moderated
i'm trying to pass a date parameter using a calendar, but it's not working !!!!

i have two parameter : from ,to

:P_from ,:P_To
below how i use it in my SQL query .
TO_CHAR(T8.ATTRIB_13,'dd/mm/yyyy') BETWEEN NVL(:P_FROM,TO_CHAR(T8.ATTRIB_13,'dd/mm/yyyy')) AND NVL(:P_TO,TO_CHAR (T8.ATTRIB_13,'dd/mm/yyyy'))

i already identified the two parameter as a date as the following:

Identifier -->P_FROM
Data Type --> DATE
Default Value *
Parameter Type-->Date

Date Setting
Display Label -->From
Text Field Size
Date Format String--> dd/mm/yyyy (must be Java date format, e.g MM-dd-yyyy)
Date From 1/01/2007
Date To 31/12/2020

PLZ Help !!
  • 1. Re: how to use the calendar in Publisher ???
    964803 Newbie
    Currently Being Moderated
    Hello

    Can you try using Uppercase "MM" for the month. Lowercase "mm" gives you Minutes.

    Date Format String--> dd/MM/yyyy

    and try using to_char for parameters also
    TO_CHAR(T8.ATTRIB_13,'dd/mm/yyyy') BETWEEN NVL(TO_CHAR(:P_FROM,'dd/mm/yyyy'),TO_CHAR(T8.ATTRIB_13,'dd/mm/yyyy')) AND NVL(TO_CHAR(:P_TO,'dd/mm/yyyy'),TO_CHAR (T8.ATTRIB_13,'dd/mm/yyyy'))

    It should work...

    Edited by: Sri S on Dec 10, 2012 10:57 PM
  • 2. Re: how to use the calendar in Publisher ???
    AlexAnd Guru
    Currently Being Moderated
    >
    TO_CHAR(T8.ATTRIB_13,'dd/mm/yyyy') BETWEEN NVL(:P_FROM,TO_CHAR(T8.ATTRIB_13,'dd/mm/yyyy')) AND NVL(:P_TO,TO_CHAR (T8.ATTRIB_13,'dd/mm/yyyy'))
    >
    you compare string with range of string

    >
    i'm trying to pass a date parameter using a calendar, but it's not working !!!!
    >
    of course


    you must compare date with range of date

    as :P_from ,:P_To are date parameters
    and if T8.ATTRIB_13 is date then use
    T8.ATTRIB_13 BETWEEN NVL(:P_FROM,T8.ATTRIB_13) AND NVL(:P_TO,T8.ATTRIB_13)
    if T8.ATTRIB_13 is not date then convert it like
    to_date(T8.ATTRIB_13, <your_mask>)
    and then compare
  • 3. Re: how to use the calendar in Publisher ???
    964803 Newbie
    Currently Being Moderated
    yes Alex you are right.

    Raad Hijazi,
    you can also try this way..Convert them to proper format and compare.

    TO_CHAR(T8.ATTRIB_13,'yyyymmdd') BETWEEN NVL(TO_CHAR(:P_FROM,'yyyymmdd'),TO_CHAR(T8.ATTRIB_13,'yyyymmdd')) AND NVL(TO_CHAR(:P_TO,'yyyymmdd'),TO_CHAR (T8.ATTRIB_13,'yyyymmdd'))

    Edited by: Sri S on Dec 10, 2012 10:59 PM
  • 4. Re: how to use the calendar in Publisher ???
    AlexAnd Guru
    Currently Being Moderated
    >
    Convert them to proper format and compare.

    TO_CHAR(T8.ATTRIB_13,'yyyymmdd') BETWEEN NVL(TO_CHAR(:P_FROM,'yyyymmdd'),TO_CHAR(T8.ATTRIB_13,'yyyymmdd')) AND NVL(TO_CHAR(:P_TO,'yyyymmdd'),TO_CHAR (T8.ATTRIB_13,'yyyymmdd'))
    >
    why do you want to explicit convert to string then implicit convert to number
    your approach is compare of number with range of numbers

    also your approach work for 'yyyymmdd'
    say, for example, result will be incorrect for 'yyyymondd' because it will compare strings

    so compare date with date
    it's right way

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points