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

is it ok for you ; select trunc (to_Date('10.04.2012 14:56:00', 'DD.MM.YYYY HH24:MI:SS')) from dual — newuserua_ext, Mar 12 '15 at 08:29
What is the **data type** of `REPORTDATE`. Is it a DATE or STRING? — Lalit Kumar B, Mar 12 '15 at 08:32
@newuserua_ext it is not OK because it returns 29.10.2013 00:00:00 this is not solution — Veljko, Mar 12 '15 at 08:34

score 35 · Accepted Answer · answered Mar 12 '15 at 08:34

35

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>

answered Mar 12 '15 at 08:34

Lalit Kumar B

1

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 Mar 12 '15 at 08:41
please help me with this additional thing I want to now group on that column but it returns error "invalid identifier" – Veljko Mar 12 '15 at 08:42
`GROUP BY to_char(REPORTDATE, 'DD.MM.YYYY') ` – Lalit Kumar B Mar 12 '15 at 08:43
Also, this might be useful to understand nls settings, please see http://stackoverflow.com/a/28298920/3989608 – Lalit Kumar B Mar 12 '15 at 08:46
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 Mar 12 '15 at 08:48
its called "Oracle SQL comparison of DATEs returns wrong result" – Veljko Mar 12 '15 at 08:57

score 4 · Answer 2 · answered Mar 12 '15 at 08:35

4

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

answered Mar 12 '15 at 08:35

Gergely Bacso

1

This is not solution. It returns "29.10.2013 00:00:00" – Veljko Mar 12 '15 at 08:37

score 0 · Answer 3 · edited Jun 17 '16 at 01:09

0

Try this code snipppet:

 cast(datetimevariable as date)

edited Jun 17 '16 at 01:09

RBT

answered Jun 17 '16 at 00:21

Majorkumar

5

This is for SQL Server (and possibly others), not Oracle. – Maaark Jul 19 '18 at 14:14

3 Answers3