Help with this query.

mac4rfree · #1 (**permalink**) 12-26-11, 07:23

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

flyboy · #2 (**permalink**) 12-26-11, 08:26

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

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.

mac4rfree · #3 (**permalink**) 12-27-11, 02:08

Quote:

Originally Posted by flyboy

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

flyboy · #4 (**permalink**) 12-27-11, 02:14

Quote:

Originally Posted by mac4rfree

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?

Quote:

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/

mac4rfree · #5 (**permalink**) 12-27-11, 07:59

bingo!!!! that worked well.. i removed to_date function.. Thanks flyboy!!!

flyboy · #6 (**permalink**) 12-27-11, 23:45

Quote:

Originally Posted by mac4rfree

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech