CODEWITHSUNDEEP
c#sqloracle-database
D.N.E.Z
D.N.E.Z

Reputation: 63

How to convert date value to oracle date value?

I have a requirement where I need to convert a date value(14-08-2013) to (14-AUG-13). I have used the below query but its not working:

select *
from ind_holidays
where hol_date between to_date('11-08-2013', 'dd-mmm-yyyy hh24:mi:ss') AND to_date('17-08-2013', 'dd-mmm-yyyy hh24:mi:ss')
ORDER by name

Upvotes: 2

Views: 2244

Answers (3)

Mike Woodhouse
Mike Woodhouse

Reputation: 52316

Oracle's TO_DATE function expects the format string to be an match for the input.

to_date('11-08-2013', 'dd-mm-yyyy')

(note: mm for a 2-digit month number, not mmm - three-character months use mon) will convert a string into a date, which your query should be handling. The hh24:mi:ss part isn't necessary, as you don't have a time component in your string. Oracle 11g on my machine still handles it though.

But your question says

I need to convert a date value(14-08-2013) to (14-AUG-13)

...which looks like is might be an output issue? A date column in the output, perhaps? The same formatting strings apply, for example:

SELECT TO_CHAR(hol_date, 'DD-MON-YY') FROM ...

Upvotes: 2

schurik
schurik

Reputation: 7928

the time part in to_date format string is unnecessary. change your query like this:

select *
from ind_holidays
where hol_date between to_date('11-08-2013', 'dd-mm-yyyy') AND to_date('17-08-2013', 'dd-mm-yyyy')
ORDER by name

and use mm instead of mmm for month in 2 digits

Upvotes: 2

Soner Gönül
Soner Gönül

Reputation: 98750

Try like this;

to_date('11-08-2013', 'DD-MON-YY')

D - Day of the month

MON - Month in three characters, i.e. "Jan","Feb", "Apr

YY - Last two digits of the year.

Upvotes: 1

Related Questions