I am using oracle 10g How can I convert a date in SQL from this format 2022-04-01 to this format 01-APR-22?

My query is:

select DRCR_CR_DT
from PS_DRCR
where TRUNC(TO_DATE(DRCR_CR_DT, 'DD/MM/YYYY:HH24:MI:SS'))
    between TO_DATE('01-APR-22','dd/mm/yyyy hh:mi:ss am')
        and TO_DATE('01-JUN-22','dd/mm/yyyy hh:mi:ss am');

This request works because in the database a have this format 01-APR-22; but in between value in the place of 01-APR-22 I want to pass value in this format 2022-04-01.

CodePudding user response：

If DRCR_CR_DT is really a DATE then you should not be doing TO_DATE(DRCR_CR_DT, ...) as that relies on implicit conversion. You are really doing

TO_DATE(TO_CHAR(DRCR_CR_DT, <NLS_DATE_FORMAT>), 'DD/MM/YYYY:HH24:MI:SS')

where NLS_DATE_FORMAT is your session setting, so the behaviour will be unpredictable with different clients and users.

You can just TRUNC(DRCR_CR_DT), though there doesn't seem to be any point with the range you are using - at least if you didn't intend to include all data from June 1st. BETWEEN is inclusive, so if you want data up to - but not including - 2022-06-01 00:00:00 then you can do:

select DRCR_CR_DT
from PS_DRCR
where DRCR_CR_DT >= DATE '2022-04-01'
and DRCR_CR_DT < DATE '2022-06-01';

If you want to pass the fixed dates in as strings - from your application - then you convert those still, but the format mask should match the value, which you suggest should be YYYY-MM-DD:

select DRCR_CR_DT
from PS_DRCR
where DRCR_CR_DT >= TO_DATE(:date_from, 'YYYY-MM-DD')
and DRCR_CR_DT < TO_DATE(:date_to, 'YYYY-MM-DD');

and pass the two string '2022-04-01' and '2022-06-01'. Or if you always want two months you could do it from just one value:

select DRCR_CR_DT
from PS_DRCR
where DRCR_CR_DT >= TO_DATE(:date_from, 'YYYY-MM-DD')
and DRCR_CR_DT < ADD_MONTHS(TO_DATE(:date_from, 'YYYY-MM-DD'), 2);

CodePudding user response：

How can I convert a date in SQL from this format 2022-04-01 to this format 01-APR-22?

You cannot because a DATE is a binary data-type that is not stored in a human-readable format. A DATE consists of 7 bytes representing century, year-of-century, month, day, hour minute and second; and it ALWAYS has those components.

So your DATE does not have the format 01-APR-22 or 2022-04-01 because it is stored in 7 bytes without a format and will have a time component.

---

If we rephrase the question to:

How can I convert a DATE in SQL that is displayed in this format 2022-04-01 to display in this format 01-APR-22?

If you do actually do have a date and you want to change how it is displayed then you can either:

1. Change the default format which the client application you are using to access the database uses to format dates. A database stores the DATE as a binary value and passes it to the client application as that binary value; the database knows nothing about displaying dates as that is something the client application does.

   If you want to change how dates are displayed then change the preferences in your client application.

   You do not say what client application you are using to access the database but if it is SQL*Plus or SQL Developer then you can use:

   ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS'
   

   For an ISO8601 format or:

   ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR'
   

   To your desired format.

   For other (non-Oracle) client applications you will need to look at their preferences or read the manual.

2. Change from using a DATE data-type to a data type that can contain formatting such as a string. For that, you can use TO_CHAR:

   select TO_CHAR(DRCR_CR_DT, 'DD-MON-RR')
   from   PS_DRCR
   where  DRCR_CR_DT >= DATE '2022-04-01' 
   and    DRCR_CR_DT <  DATE '2022-06-02';
   

---

You said:

My query is:

select DRCR_CR_DT
from PS_DRCR
where TRUNC(TO_DATE(DRCR_CR_DT, 'DD/MM/YYYY:HH24:MI:SS'))
    between TO_DATE('01-APR-22','dd/mm/yyyy hh:mi:ss am')
        and TO_DATE('01-JUN-22','dd/mm/yyyy hh:mi:ss am');

This request works because in the database a have this format 01-APR-22; but in between value in the place of 01-APR-22 I want to pass value in this format 2022-04-01.

That is not true.

Your query works because of the string-to-date conversion rules which state:

• You can omit time fields found at the end of a format string from the date string.

and

• If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 2-20.

Original Format Element	Additional Format Elements to Try in Place of the Original
'MM'	'MON' and 'MONTH'
'MON'	'MONTH'
'MONTH'	'MON'
'YY'	'YYYY'
'RR'	'RRRR'

So the time component of the format models will be ignored and the MM format model will also match MON but even then it still does not "work" as expected as:

TO_DATE('01-APR-22','dd/mm/yyyy hh:mi:ss am')

Will convert from the string 01-APR-22 to the date 0022-04-01 where the century is the 1st century AD.

It only appears to work because there is a second error in the query as you are also using TO_DATE on a value that is already a date:

TO_DATE(DRCR_CR_DT, 'DD/MM/YYYY:HH24:MI:SS')

Will actually perform:

TO_DATE(
  TO_CHAR(
    DRCR_CR_DT,
    (SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT')
  ),
  'DD/MM/YYYY:HH24:MI:SS'
)

(since TO_DATE expects a string as the first argument and not a date so there will be an implicit conversion from date-to-string before TO_DATE converts it back to a date.)

This means that if your NLS_DATE_FORMAT is DD-MON-RR then your query will convert a date with, for example, the value 1922-05-13T45:32:16 to a string with the format DD-MON-RR which would be 13-MAY-22 and then convert it back to a date using the format model 'DD/MM/YYYY:HH24:MI:SS' to the date 0022-05-13. Given that your bounds are converted to 0022-04-01 and 0022-06-01 then this would be between them.

You need to fix both the errors in your query as only fixing one of them will mean that it will not return any rows (as opposed to the current query which just ignores the century in the dates).

---

What you want to do is leave the date as a date and to use TO_DATE with the appropriate format model:

select TO_CHAR(DRCR_CR_DT, 'YYYY-MM-DD')
from   PS_DRCR
where  TRUNC(DRCR_CR_DT) BETWEEN TO_DATE('2022-04-01', 'YYYY-MM-DD')
                             AND TO_DATE('2022-06-01', 'YYYY-MM-DD');

or

select TO_CHAR(DRCR_CR_DT, 'YYYY-MM-DD')
from   PS_DRCR
where  DRCR_CR_DT >= TO_DATE('2022-04-01', 'YYYY-MM-DD')
and    DRCR_CR_DT <  TO_DATE('2022-06-02', 'YYYY-MM-DD');

(which can use an index on the DRCR_CR_DT column, whereas the previous query would not use an index on the DRCR_CR_DT column but would require a function-based index on TRUNC(DRCR_CR_DT) instead.)

Or, using DATE literals:

select TO_CHAR(DRCR_CR_DT, 'YYYY-MM-DD')
from   PS_DRCR
where  DRCR_CR_DT >= DATE '2022-04-01'
and    DRCR_CR_DT <  DATE '2022-06-02';

Or, TIMESTAMP literals:

select TO_CHAR(DRCR_CR_DT, 'YYYY-MM-DD')
from   PS_DRCR
where  DRCR_CR_DT >= TIMESTAMP '2022-04-01 00:00:00'
and    DRCR_CR_DT <  TIMESTAMP '2022-06-02 00:00:00';