extract date only from given date in oracle sql

What is the best Oracle sql query to extract date? input entry – 2020-10-14T07:26:32.661Z , expected output – 2020-10-14

Answer

If you want a DATE data type where the time component is midnight then:

SELECT TRUNC(
         TO_TIMESTAMP_TZ(
           '2020-10-14T07:26:32.661Z', 
           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZR'
         )
       ) AS truncated_date
FROM   DUAL;

Which (depending on your NLS_DATE_FORMAT) outputs:

| TRUNCATED_DATE      |
| :------------------ |
| 2020-10-14 00:00:00 |

(Note: a DATE data type has year, month, day, hour, minute and second components. Whatever client program you are using to access the database may choose not to show the time component but it will still be there.)

If you want a YYYY-MM-DD formatted string then:

SELECT TO_CHAR(
         TO_TIMESTAMP_TZ(
           '2020-10-14T07:26:32.661Z', 
           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZR'
         ),
         'YYYY-MM-DD'
       ) AS formatted_date
FROM   DUAL;

| FORMATTED_DATE |
| :------------- |
| 2020-10-14     |

db<>fiddle here