How to filter data based on difference of two date Column when date or timestamp values are given in Traditional Chinese format.(in Oracle)

 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

========