11 Replies Latest reply: Feb 8, 2013 11:53 AM by Mo Koteiche RSS

    Locale specific query

    Mo Koteiche
      Hi,

      I'm trying to get all records in a week based on a user provided parameter (dayInWeek).
      My query works fine, however it does not take care of locale changes. I'm using static numbers to get the dates from Monday (2) to Sunday (8)
      SELECT Attendance.RECORD_ID, 
             Attendance.DATETIME, 
             Attendance.ACTION, 
             Attendance.EMPLOYEE_ID
      FROM ATTENDANCE Attendance
      WHERE Attendance.EMPLOYEE_ID = :currEmployeeId
      AND Attendance.DATETIME >= to_char(:dayInWeek+ (2-to_char(:dayInWeek,'D')),'DD-Mon-YYYY')
      AND Attendance.DATETIME <= to_char(:dayInWeek+ (8-to_char(:dayInWeek,'D')),'DD-Mon-YYYY')
      ORDER BY Attendance.DATETIME
      Is there a way to work around this?
      I'm using jDeveloper 11.1.2.3.0

      Thanks,
      Mo
        • 1. Re: Locale specific query
          Etbin
          use ISO week http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG212

          Regards

          Etbin
          • 2. Re: Locale specific query
            BluShadow
            Your query won't work properly anyway... you're treating dates as strings rather than DATE datatypes.
            AND Attendance.DATETIME >= to_char(:dayInWeek+ (2-to_char(:dayInWeek,'D')),'DD-Mon-YYYY')
            AND Attendance.DATETIME <= to_char(:dayInWeek+ (8-to_char(:dayInWeek,'D')),'DD-Mon-YYYY')
            Not quite sure how you are expecting a day of the week (which is a number) to be converted to a string with the to_char function and a format of 'DD-Mon-YYYY'. Such a date format would be expecting a DATE to be provided in the TO_CHAR function. However, why TO_CHAR? Why not TO_DATE to turn your calculated date into a DATE datatype? ... and just how are you expecting to turn the day in the week into a DATE?
            • 3. Re: Locale specific query
              Frank Kulash
              Hi,

              Welcome to the forum!
              986964 wrote:
              Hi,

              I'm trying to get all records in a week based on a user provided parameter (dayInWeek).
              My query works fine, however it does not take care of locale changes. I'm using static numbers to get the dates from Monday (2) to Sunday (8)
              Sorry, it's unclear what you mean.
              The results of <tt> TO_CHAR (dt, 'D') </tt> depend on NLS_TERRITORY. In The US, Friday is day '6', but in Poland, Friday is (literally) day '5'. Are you looking for something that will find a Monday-to-Sunday week, without knowing what NLS_TERRITORY might be?
              If so, use <tt> TRUNC (dt, 'IW') </tt>.
              SELECT    record_id, 
                            datetime, 
                            action, 
                            employee_id
              FROM          attendance
              WHERE        employee_ID     = :currEmployeeId
              AND        datetime      >= TRUNC ( TO_DATE (:dayInWeek, 'DD-Mon-YYYY')
                                      , 'IW'
                                   )
              AND       datetime      <  TRUNC ( TO_DATE (:dayInWeek, 'DD-Mon-YYYY')
                                      , 'IW'
                                   ) + 7
              ORDER BY  datetime
              ;
              This assumes that datetime is a DATE (or TIMESTAMP) and that :dayInWeek is a VARCHAR2 such as '08-Feb-2013'.

               

              I hope this answers your question.
              If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
              Explain, using specific examples, how you get those results from that data.
              Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
              Include this information whenever you have a question.
              See the forum FAQ {message:id=9360002}
              • 4. Re: Locale specific query
                Mo Koteiche
                Hey Frank,

                Thank you for your kind reply.

                You guessed right, I'm looking for the Monday-to-Sunday week regardless of the NLS_TERRITORY
                Actually Datetime is a DATE and dayInWeek is a TIMESTAMP

                Here's my table:
                CREATE TABLE attendance
                    ( record_id            NUMBER(6) NOT NULL
                     , employee_id   NUMBER(3) NOT NULL    
                    , datetime          DATE NOT NULL
                     , action           VARCHAR2(1) NOT NULL
                    ) 
                hope this is more clear,

                Thanks again
                Mo
                • 5. Re: Locale specific query
                  Frank Kulash
                  Hi,
                  986964 wrote:
                  Hey Frank,

                  Thank you for your kind reply.

                  You guessed right, I'm looking for the Monday-to-Sunday week regardless of the NLS_TERRITORY
                  Actually Datetime is a DATE and dayInWeek is a TIMESTAMP
                  If dayInWeek is compared to a DATE column, why not make it a DATE?
                  Not that it really matters; <tt> TRUNC (dt, 'IW') </tt> is designed to work whether the dt is a DATE or a TIMESTAMP. So, depending on your data and your requirements, you can use the query I posted earlier, but without TO_DATE:
                  SELECT    record_id, 
                                datetime, 
                                action, 
                                employee_id
                  FROM          attendance
                  WHERE        employee_ID     = :currEmployeeId
                  AND        datetime      >= TRUNC (dayInWeek, 'IW')
                  AND       datetime      <  TRUNC (dayInWeek, 'IW') + 7 
                  ORDER BY  datetime
                  ;
                  <tt> TRUNC (dt, 'IW') </tt> always returns a DATE, even whn dt is a TIMESTAMP, so the comparisons with datetime will be efficient.
                  • 6. Re: Locale specific query
                    Mo Koteiche
                    Hey Frank,

                    Thanks again,

                    My query is now like this :
                    SELECT Attendance.RECORD_ID, 
                               Attendance.DATETIME, 
                               Attendance.ACTION, 
                               Attendance.EMPLOYEE_ID
                    FROM ATTENDANCE Attendance
                    WHERE Attendance.EMPLOYEE_ID = :currEmployeeId
                    AND Attendance.DATETIME >= TRUNC (:dayInWeek, 'IW')
                    AND Attendance.DATETIME < TRUNC (:dayInWeek, 'IW') + 7
                                            
                    ORDER BY Attendance.DATETIME
                    However i'm getting the following error:
                    Error at line 8, column 24:
                    ORA-00932: inconsistent datatypes: expected DATE got NUMBER

                    I tried looking up the problem online, but didn't find anything clear.

                    Any thoughts?

                    Regards,
                    Mo
                    • 7. Re: Locale specific query
                      Frank Kulash
                      Hi, Mo,

                      This is exactly why you need to post CREATE TABLE and INSERT statementts for your sample data (and parameters, such as :DayInWeek).
                      The CREATE TABLE statement you posted earlier looks good. Now post some INSERT statements, and whatever code is necessary to create and set the parameters, so that the people who want to help you can re-create the problem and test their ideas.
                      • 8. Re: Locale specific query
                        Mo Koteiche
                        Hi Frank,

                        Sorry about that,

                        here's a sample insert statements:
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/25 08:30:44', 'yyyy/mm/dd hh24:mi:ss')), 1);
                        
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/25 18:15:23', 'yyyy/mm/dd hh24:mi:ss')), 0);
                        
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/26 08:40:12', 'yyyy/mm/dd hh24:mi:ss')), 1);
                        
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/26 18:02:34', 'yyyy/mm/dd hh24:mi:ss')), 0);
                        
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/27 09:31:53', 'yyyy/mm/dd hh24:mi:ss')), 1);
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/27 09:31:54', 'yyyy/mm/dd hh24:mi:ss')), 1);
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/27 09:31:55', 'yyyy/mm/dd hh24:mi:ss')), 1);
                        
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/27 18:01:41', 'yyyy/mm/dd hh24:mi:ss')), 0);
                        
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/28 09:05:30', 'yyyy/mm/dd hh24:mi:ss')), 1);
                        
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/02/28 19:30:44', 'yyyy/mm/dd hh24:mi:ss')), 0);
                        
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/03/01 09:05:30', 'yyyy/mm/dd hh24:mi:ss')), 1);
                        
                        INSERT INTO ATTENDANCE (EMPLOYEE_ID, DATETIME, ACTION) VALUES
                        (1, (to_date('2013/03/01 19:00:21', 'yyyy/mm/dd hh24:mi:ss')), 0);
                        Create table:
                        CREATE TABLE attendance
                            ( record_id            NUMBER(6) NOT NULL
                             , employee_id   NUMBER(3) NOT NULL    
                            , datetime          DATE NOT NULL
                             , action           VARCHAR2(1) NOT NULL
                            ) 
                        / 
                        
                        ALTER TABLE ATTENDANCE
                        ADD CONSTRAINT ATTENDANCE_PK PRIMARY KEY
                        (
                        RECORD_ID
                        )
                         ENABLE
                        /
                        
                        ALTER TABLE ATTENDANCE
                        ADD CONSTRAINT ATTENDANCE_EMPLOYEE_ID_FK FOREIGN KEY
                        (
                        EMPLOYEE_ID
                        )
                        REFERENCES EMPLOYEE
                        (
                        EMPLOYEE_ID
                        )
                        ON DELETE SET NULL ENABLE
                        /
                        Btw, I'm working on oracle ADF, and this is a view Object query.
                        dayInWeek is a bind variable of type Timestamp for the sql query for this object.

                        Thank you, I really appreciate your help.
                        • 9. Re: Locale specific query
                          Frank Kulash
                          Hi,

                          I suspect the problem is with how dayInWeek is defined or set in ADF.
                          I don't have acess to ADF, and I can't re-create the problem in SQL*Plus.

                          The INSERT statements you posted all raise errors with the CREATE TABLE statement you posted (absent a trigger). I created the table like this:
                          CREATE TABLE attendance
                              ( record_id          NUMBER(6)     -- NOT NULL
                              , employee_id        NUMBER(3)      NOT NULL    
                              , datetime          DATE           NOT NULL
                              , action           VARCHAR2(1)      NOT NULL
                              ) 
                          / 
                          without any additional constraints, just so I could populate the table with your INSERT statements.
                          This query:
                          WITH     params          AS
                          (
                               SELECT  1                    AS currEmployeeId
                               ,     TIMESTAMP '2013-02-28 21:00:00'     AS dayInWeek
                               FROM     dual
                          )
                          SELECT    record_id, 
                                        datetime, 
                                        action, 
                                        employee_id
                          FROM          attendance
                          CROSS JOIN     params
                          WHERE        employee_ID     = currEmployeeId
                          AND        datetime      >= TRUNC (dayInWeek, 'IW')
                          AND       datetime      <  TRUNC (dayInWeek, 'IW') + 7 
                          ORDER BY  datetime
                          ;
                          produced these results:
                          `RECORD_ID DATETIME            A EMPLOYEE_ID
                          ---------- ------------------- - -----------
                                     2013/02/25 08:30:44 1           1
                                     2013/02/25 18:15:23 0           1
                                     2013/02/26 08:40:12 1           1
                                     2013/02/26 18:02:34 0           1
                                     2013/02/27 09:31:53 1           1
                                     2013/02/27 09:31:54 1           1
                                     2013/02/27 09:31:55 1           1
                                     2013/02/27 18:01:41 0           1
                                     2013/02/28 09:05:30 1           1
                                     2013/02/28 19:30:44 0           1
                                     2013/03/01 09:05:30 1           1
                                     2013/03/01 19:00:21 0           1
                          If I lose the sub-query and the cross join, and use literals (or substitution variables) for the parameters, I get the same results.

                          The error message you received is pretty clear (though, of course, that doesnt mean it's accurate). But given that the SQL statement works when dayInWeek is clearly a TIMESTAMP, then it sure sounds like dayInWeek is not a TIMESTAMP when you get the error.
                          • 10. Re: Locale specific query
                            chris227
                            Mo Koteiche wrote:
                            However i'm getting the following error:
                            Error at line 8, column 24:
                            ORA-00932: inconsistent datatypes: expected DATE got NUMBER

                            I tried looking up the problem online, but didn't find anything clear.

                            Any thoughts?
                            You got the Date in milliseconds from Java.
                            There are two ways:
                            1. You configure in the ADF something to convert the date inan oracle SQL-date, something i would prefer.
                            2. You calculate the date from the milliseconds with something like:
                            select
                            to_date('01011970','DDMMYYYY')
                            +
                            numtodsinterval( java_milliseconds/1000,'SECOND')
                            from dual
                            • 11. Re: Locale specific query
                              Mo Koteiche
                              Thank you both,

                              Chris, what you mentioned is indeed correct, i was getting the date as milliseconds from java.

                              The query is now functioning properly.

                              Frank, thank you so much for your time, for i would have not gotten to the correct answer without your help.

                              Regards,
                              Mo