4 Replies Latest reply: Dec 11, 2012 1:14 AM by AlexAnd RSS

    how to use the calendar in Publisher ???

    950017
      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
          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
            >
            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
              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
                >
                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