Literal does not match format string error When calculating the difference between two dates in oracle

Question

I want to calculate the difference between two dates, and get the day difference between these dates in Oracle. Here is my code:

Select To_Date(TO_CHAR(sysdate, 'YYYY/MM/DD','nls_calendar=persian'))- TO_Date(TO_CHAR(SHF_Date))
from DtTable

But I get this error:

ORA-01861: literal does not match format string for case statement

Answer 1

sysdate is already a date, and shf_date also seems to be one. You don't need any conversion here, and can subtract them directly:

SELECT sysdate - SHF_Date FROM DtTable

Answer 2

If you want to use your statement

Select To_Date( TO_CHAR(sysdate, 'YYYY/MM/DD','nls_calendar=persian'), 'YYYY/MM/DD')- TO_Date(TO_CHAR(SHF_Date, 'YYYY/MM/DD'), 'YYYY/MM/DD') from DtTable;

But it does not make any sense.

Use it in this way

Select sysdate - hire_date from employees;

Answer 3

Storage format and display format are two different things. We let Oracle store data using its own internal format, which for DATE columns is a 7-byte binary value that is not human-readable.

How you choose to present the values in a report or application is another thing - for example, you could display the same number as 1000, 1,000 or 1e3, or display the same date as 2021-04-18 or Sunday 18th April. This is not related to how you store it. You cannot store any preferred display format. You have to handle the display format when you query it or in your application.

Also, in Oracle date arithmetic, subtracting one date from another gives a number of days, so trying to query to_date(date2 - date1) will never work, because how can it convert a number of days like 270 into a date?

create table dttable (shf_date date);

insert into dttable (shf_date)
values (to_date('1399/05/01','YYYY-MM-DD', 'nls_calendar=Persian'));

(Notice the definition of shf_date as an Oracle date. Is that how it is in your table?)

select to_char(shf_date, 'YYYY-MM-DD', 'nls_calendar=Gregorian') as shf_date_gregorian
     , to_char(shf_date, 'YYYY-MM-DD', 'nls_calendar=Persian') as shf_date_shamsi
     , trunc(sysdate - shf_date) as days_since_shf_date
from   dttable;

SHF_DATE_GREGORIAN   SHF_DATE_SHAMSI DAYS_SINCE_SHF_DATE
-------------------- --------------- -------------------
2020-07-22           1399-05-01                      270

Answer 4

Although you've said several times that shf_date is a date, you've also said things like:

sysdate is : DD/MM/YYYY but shf_date is : YYYY/MM/DD

I run this code: select trunc(sysdate - shf_date) from dttable; but I get 'Invalid number' error.

Which means it is not a date, but is stored as a string. So, you need to convert that string to a date, in Gregorian calendar, so that actual date can be compared with sysdate.

Modifying and expanding William's example:

select sysdate as sysdate_date
     , to_char(sysdate, 'YYYY-MM-DD', 'nls_calendar=Gregorian') as sysdate_gregorian
     , to_char(sysdate, 'YYYY-MM-DD', 'nls_calendar=Persian') as sysdate_shamsi
     , shf_date as shf_date_string
     , to_date(shf_date, 'YYYY/MM/DD', 'nls_calendar=Persian') as shf_date_date
     , to_char(to_date(shf_date, 'YYYY/MM/DD', 'nls_calendar=Persian'), 'YYYY-MM-DD', 'nls_calendar=Gregorian') as shf_date_gregorian
     , to_char(to_date(shf_date, 'YYYY/MM/DD', 'nls_calendar=Persian'), 'YYYY-MM-DD', 'nls_calendar=Persian') as shf_date_shamsi
     , trunc(sysdate - to_date(shf_date, 'YYYY/MM/DD', 'nls_calendar=Persian')) as days_since_shf_date
from   dttable;

SYSDATE_DATE | SYSDATE_GREGORIAN | SYSDATE_SHAMSI | SHF_DATE_STRING | SHF_DATE_DATE | SHF_DATE_GREGORIAN | SHF_DATE_SHAMSI | DAYS_SINCE_SHF_DATE
:----------- | :---------------- | :------------- | :-------------- | :------------ | :----------------- | :-------------- | ------------------:
18-APR-21    | 2021-04-18        | 1400-01-29     | 1399/05/01      | 22-JUL-20     | 2020-07-22         | 1399-05-01      |                 270

db<>fiddle

If you want to use this in a filter then you can do:

where trunc(sysdate - to_date(shf_date, 'YYYY/MM/DD', 'nls_calendar=Persian')) = 270

or whatever comparison you need to perform.