Some of the Chinese Date format that should be inside the column where we do the filtering.
select sysdate from dual
10-6月 -2021 is 10th of June 2021
22-10月 -20
24-9月 -20 02.42.58.000000000 上午
453642 07-12月-20 06.54.59.000000000 上午 07-12月-20 06.58.00.000000000 上午
We can filter based on the below SQL:
---One way is to convert the Traditional Chinese date to English calender date and compare.
select * from USER_DET_TABLES where Start_Datetime between to_date('22-OCT-20','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''American'' ') and to_date('25-FEB-21','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''American'' ')
--Other way is to directly pass the Traditional Chinese date in To_Date() and use NLS_DATE_LANGUAGE
select * from jobstatus_users_tab where Start_Datetime between to_date('22-10月 -20','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''Traditional Chinese'' ') and to_date('24-2月 -21','DD-MON-YY', 'NLS_DATE_LANGUAGE = ''Traditional Chinese'' ')
==
Specifying NLS Parameters in SQL Functions and Valid Use case of NLS parameters in SQL functions in Oracle
NLS parameters are specified in SQL functions as 'parameter = value'. For example:
'NLS_DATE_LANGUAGE = AMERICAN'
The following NLS parameters can be specified in SQL functions:
NLS_DATE_LANGUAGE
NLS_NUMERIC_CHARACTERS
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_DUAL_CURRENCY
NLS_CALENDAR
NLS_SORT
Table: SQL Functions and Their Valid NLS Parameters shows which NLS parameters are valid for specific SQL functions.
SQL Functions and Their Valid NLS Parameters
SQL Function Valid NLS Parameters
TO_DATE NLS_DATE_LANGUAGE, NLS_CALENDAR
TO_NUMBER NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY,
TO_CHAR NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR
TO_NCHAR NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR
NLS_UPPER NLS_SORT
NLS_LOWER NLS_SORT
NLS_INITCAP NLS_SORT
NLSSORT NLS_SORT
========
