CODEWITHSUNDEEP
sqloracle-database
Aadhi
Aadhi

Reputation: 15

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

Upvotes: 0

Views: 145

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The canonical way is probably trunc():

select trunc(input_entry)

This assumes that input_entry is a date or timestamp value.

EDIT:

If your input is just a string, use string operations:

select substr(input_entry, 1, 10)

You can also readily cast this to a date.

Upvotes: 0

MT0
MT0

Reputation: 167832

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

Upvotes: 1

Related Questions