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

Solution 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>

Solution 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

Comments

[Veljko]

This is not solution. It returns "29.10.2013 00:00:00"

[Veljko]

Thank you this is solution. May I ask you additionnal question I am trying to do the group by with this date column but not succeed please look "SELECT to_char(REPORTDATE, 'DD.MM.YYYY') AS MYDATE, COUNT(*) from TABLE1 GROUP BY MYDATE" it returns Invalid identifier

[Veljko]

please help me with this additional thing I want to now group on that column but it returns error "invalid identifier"

[Lalit Kumar B]

GROUP BY to_char(REPORTDATE, 'DD.MM.YYYY')

[Lalit Kumar B]

Also, this might be useful to understand nls settings, please see stackoverflow.com/a/28298920/3989608

[Veljko]

thank you and one more thing (last I promise). If I want to do the comparation for example I want to see dates later than "09.11.2013" but it returns me as result also and 30.10.2013 Expression I used was: SELECT to_char(REPORTDATE, 'DD.MM.YYYY'), COUNT(*) from TABLE where to_char(REPORTDATE, 'DD.MM.YYYY')>'09.11.2013' GROUP BY to_char(REPORTDATE, 'DD.MM.YYYY')

[Veljko]

its called "Oracle SQL comparison of DATEs returns wrong result"

[Maaark]

This is for SQL Server (and possibly others), not Oracle.