Seyren Windsor Seyren Windsor - 6 months ago 46
MySQL Question

"day of month must be between 1 and last day of month" when looking for time in Oracle

This may be rather simple, but i just don't get how to fix this.

So this is rather a simple query i'm doing:

SELECT TO_CHAR(DATE,'DD-MM-YYYY') AS DATE, TO_CHAR(TIME,'HH24:MI') AS TIME,
CODEROOM, GROUP0, GROUP1, GROUP2, GROUP3,
FROM CONCERTS
WHERE DATE = '20-05-16' AND TIME = '00:00' AND CODEROOM = '1'


Now, if i do the exact same query removing "AND TIME = '00:00'", everything works completely fine, but if i try to search for TIME, it throws the following error:

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


I know i can group in the same column TIME and DATE, and i tried and i did not manage to fix it either.

I seriously don't know how to fix that, i know it can be because DATEs in Oracle store DATE and TIME, and it could have an invalid DATE despite having an okay time, but if that was the case, the error would be thrown when executing the query, and i can't even get to execute it (I also checked the dates were valid anyway)

Is there any way to fix this? i've been looking up and i can't seem to find a solution to this

Thanks a lot in advance :)

Answer

First, You may first want to change your column definitions so that you have meaningful name such as CONCERT_DATE or CONCERT_TIME

Second, You would need to apply the same to_char function on your where clause as well..

SELECT TO_CHAR(DATE,'DD-MM-YYYY') AS CONCERT_DATE, TO_CHAR(TIME,'HH24:MI') AS CONCERT_TIME,
CODEROOM, GROUP0, GROUP1, GROUP2, GROUP3,
FROM CONCERTS
WHERE TO_CHAR(DATE,'DD-MM-YYYY') = '20-05-2016' 
  AND TO_CHAR(TIME,'HH24:MI') = '00:00'  
  AND CODEROOM = '1'