CODEWITHSUNDEEP
sqloracle11g
developer
developer

Reputation: 9478

date conversion from DD-MM-YYYY to yyyymm

Below is the query i tried but iam getting error. Please correct me.

select to_char(to_date(01-03-2018, 'DD-MM-YYYY'), 'yyyymm') from dual;

Getting below exception

ORA-01847: day of month must be between 1 and last day of month
01847. 00000 -  "day of month must be between 1 and last day of month"

Upvotes: 0

Views: 1307

Answers (2)

sev3ryn
sev3ryn

Reputation: 1087

You missed quotes around date. That's why Oracle treats 01-03-2018 as a number and evaluates it to 1-3-2018 = -2020 and then tries to treat -2020 as the day number of the current month since no other date elements appear to be present. Real query that oracle execute behind yours is:

select to_char(to_date(to_char(1-3-2018), 'DD-MM-YYYY'), 'yyyymm') from dual;

And the correct query is

select to_char(to_date('01-03-2018', 'DD-MM-YYYY'), 'yyyymm') from dual;

Thanks to @Alex Poole who corrected this explanation.

Upvotes: 2

Multisync
Multisync

Reputation: 8787

select to_char(to_date('01-03-2018', 'DD-MM-YYYY'), 'yyyymm') from dual;

The first argument of TO_DATE function should be from the char group so you need to use quotes.

Upvotes: 3

Related Questions