Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: Help with this query.

    Hi guys,

    I am trying to find the day of the week.

    Code:
    SQL> SELECT TO_CHAR(TO_DATE('2009/11/08','yyyy/MM/dd'),'D'),
    to_char(CURR_YR_ACTUAL_SALES_DATE,'yyyy/MM/dd') FROM selling_calendar 
    WHERE CURR_YR_ACTUAL_SALES_DATE='08-NOV-09';
    
    T TO_CHAR(CU
    - ----------
    1 2009/11/08
    [The above query is giving correct result as 1.

    But when i run the below query without hardcoding, it is giving 6.

    Code:
    SQL> SELECT TO_CHAR(TO_DATE(CURR_YR_ACTUAL_SALES_DATE,'yyyy/MM/dd'),'D'),
    to_char(CURR_YR_ACTUAL_SALES_DATE,'yyyy/MM/dd') FROM selling_calendar
     WHERE CURR_YR_ACTUAL_SALES_DATE='08-NOV-09';
    
    T TO_CHAR(CU
    - ----------
    6 2009/11/08
    Can somebody let me know what i am doing wrong.

    Cheers!!!!!

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    What is the data type of SELLING_CALENDAR.CURR_YR_ACTUAL_SALES_DATE column?
    What date is '08-NOV-09'? November 9th, 2008 or November 8th, 2009? You may also be in year 8 or 9.
    What is your NLS_DATE_FORMAT setting (SELECT * FROM v$nls_parameters)?
    Quote Originally Posted by mac4rfree View Post
    Can somebody let me know what i am doing wrong.
    Most probably, implicit DATE->VARCHAR2 or VARCHAR2->DATE data type conversion. Without answering my question, this is all I can guess.

  3. #3
    Join Date
    Jul 2009
    Posts
    58
    Quote Originally Posted by flyboy View Post
    Hi,

    What is the data type of SELLING_CALENDAR.CURR_YR_ACTUAL_SALES_DATE column?
    What date is '08-NOV-09'? November 9th, 2008 or November 8th, 2009? You may also be in year 8 or 9.
    What is your NLS_DATE_FORMAT setting (SELECT * FROM v$nls_parameters)?

    Most probably, implicit DATE->VARCHAR2 or VARCHAR2->DATE data type conversion. Without answering my question, this is all I can guess.
    The second column actually answers you. Oracle is able to recognise the value as November 8th, 2009. But for some reason when i am passing it from table

    Code:
    SELECT * FROM v$nls_parameters
    NLS_DATE_FORMAT	DD-MON-RR

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by mac4rfree View Post
    The second column actually answers you. Oracle is able to recognise the value as November 8th, 2009. But for some reason when i am passing it from table

    Code:
    SELECT * FROM v$nls_parameters
    NLS_DATE_FORMAT	DD-MON-RR
    And the answer to my first question?
    What is the data type of SELLING_CALENDAR.CURR_YR_ACTUAL_SALES_DATE column?
    You are using both TO_DATE and TO_CHAR on that column, so, one of them leads to implicit conversion and therefore it is wrong. Please, read about those functions and especially their supported parameter types in SQL Language Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

  5. #5
    Join Date
    Jul 2009
    Posts
    58
    bingo!!!! that worked well.. i removed to_date function.. Thanks flyboy!!!

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by mac4rfree View Post
    bingo!!!! that worked well.. i removed to_date function..
    I am still curious: what is the data type of SELLING_CALENDAR.CURR_YR_ACTUAL_SALES_DATE column? Is it really DATE (as your posts would indicate)? Let me suppose so (in fact I hate guessing, but I want to share more about treating DATEs/VARCHAR2s), otherwise, just ignore the rest or transform it to the real data type of that column.

    Did you just blindly removed TO_DATE/TO_CHAR until it returned correct result, or realized that you were extracting day from November, 9th 0008?

    Here are some advices:
    Do not use only string literals when referencing a DATE (that '08-NOV-09' in WHERE clause - it has VARCHAR data type, hence my second question). Use TO_DATE with proper format mask to convert it to DATE data type explicitly. Now, it (or SELLING_CALENDAR.CURR_YR_ACTUAL_SALES_DATE column - this is tricky thing of implicit conversion - you cannot say which of the two values with different data types in comparison will be converted to the data type of the opposite one; converting the column will prevent use of index on that column, which would slow performance) is converted correctly as it matches NLS_DATE_FORMAT, but it may not be in all sessions for all time. Other side effects may come to play - e.g. truncating of time part when NLS_DATE_FORMAT does not contain it, but keeping it when NLS_DATE_FORMAT will be enhanced with it.

    Month abbreviations ('NOV') are dependent on NLS_DATE_LANGUAGE setting. It may work for November for most languages (not all), but be prepared to use e.g. 'DEZ' (Dezember) for December when the NLS_DATE_LANGUAGE will be set to GERMAN. Either use month numbers (11) or specify it in the third parameter of TO_DATE function.

    Also be aware that the value of 'D' (day of the week) depends on NLS_TERRITORY setting - if I recall it correctly, week starts on Sunday for NLS_TERRITORY=AMERICA, but on Monday for e.g. most European countries. However I am not aware is would be possible to set it in TO_DATE as NLS_DATE_LANGUAGE - the only way seems to be ALTER SESSION statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •