Hi ALL,
SQL> Select Banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Create Table Statement as Follows
CREATE TABLE TEST_DATES
(
CODE NUMBER(4),
DATES DATE
);
Insert into TEST_DATES(CODE, DATES) Values(1, TO_DATE('02/25/2017 12:08:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATES(CODE, DATES) Values(2, TO_DATE('02/26/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
I have written two SELECT statements one is returning rows as per the requirement , but another is not working due to the time part present in the Date column in CODE 1.
1) 1st query.
Select * FROM Test_Dates
WHERE DATES >= TO_DATE('25/02/2017','dd/mm/rrrr')
AND DATES <= TO_DATE('26/02/2017','dd/mm/rrrr');
OUTPUT
--------------
| CODE | DATES |
| 1 | 25/02/2017 12:08:00 |
| 2 | 26/02/2017 |
2) 2nd Query.
Select * FROM Test_Dates
WHERE DATES >= TO_DATE('25/02/2017','dd/mm/rrrr')
AND DATES <= TO_DATE('25/02/2017','dd/mm/rrrr');
OUTPUT
-------------
no rows selected.
SELECT * FROM NLS_DATABASE_PARAMETERS; nls parameters.
| PARAMETER | VALUE |
| NLS_LANGUAGE | AMERICAN |
| NLS_TERRITORY | AMERICA |
| NLS_CURRENCY | $ |
| NLS_ISO_CURRENCY | AMERICA |
| NLS_NUMERIC_CHARACTERS | ., |
| NLS_CHARACTERSET | WE8ISO8859P15 |
| NLS_CALENDAR | GREGORIAN |
| NLS_DATE_FORMAT | DD-MON-RR |
| NLS_DATE_LANGUAGE | AMERICAN |
| NLS_SORT | BINARY |
| NLS_TIME_FORMAT | HH.MI.SSXFF AM |
| NLS_TIMESTAMP_FORMAT | DD-MON-RR HH.MI.SSXFF AM |
| NLS_TIME_TZ_FORMAT | HH.MI.SSXFF AM TZR |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_DUAL_CURRENCY | $ |
| NLS_COMP | BINARY |
| NLS_LENGTH_SEMANTICS | BYTE |
| NLS_NCHAR_CONV_EXCP | FALSE |
| NLS_NCHAR_CHARACTERSET | AL16UTF16 |
| NLS_RDBMS_VERSION | 11.2.0.4.0 |
How to use the date columns with time part in where condition ?
What is the correct condition to use in where clause BETWEEN AND OR >= <= OR = for better performance tuning of the query ?
Another question is that --> Is it correct to keep NLS_DATE_FORMAT as DD-MON-RR or DD-MON-RRRR is correct ?
Please describe the pros and cons of the NLS_DATE_FORMAT.