This discussion is archived
11 Replies Latest reply: Feb 8, 2013 9:53 AM by Mo Koteiche RSS

Locale specific query

Mo Koteiche Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    use ISO week http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG212

    Regards

    Etbin
  • 2. Re: Locale specific query
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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