Oracle SQL "SELECT DATE from DATETIME field "

Question

I have field REPORTDATE (DATETIME). In SQL Developer i can see its value in this format

29.10.2013 17:08:08

I found that in order to do the select of just a DATE I need to execute this:

SELECT TO_DATE (REPORTDATE, 'DD.MON.YYYY') AS my_date
FROM TABLE1

but it returns 0RA-01843: not a valid month

I want result to return only 29.10.2013

Answer 1

TO_DATE (REPORTDATE, 'DD.MON.YYYY')

This makes no sense. You are converting a date into a date again. You use TO_DATE to convert a string literal into DATE.

I want result to return only 29.10.2013

You could use TRUNC to truncate the time element. If you want to use this value for DATE calculations, you could use it directly.

For example,

SQL> select TRUNC(SYSDATE) dt FROM DUAL;

DT
---------
12-MAR-15

To display in a particular format, you could use TO_CHAR and proper FORMAT MASK.

SQL> SELECT to_char(SYSDATE, 'DD.MM.YYYY') dt from dual;

DT
----------
12.03.2015

SQL>

Answer 2

Use this:

SELECT trunc(REPORTDATE, 'DD') AS my_date
FROM TABLE1

This will not change the type of the returning object, only truncates everything below "day" level.

If you are ok with returning a String, then you can just do:

SELECT TO_CHAR(REPORTDATE, 'DD.MM.YYYY') AS my_date
FROM TABLE1