CODEWITHSUNDEEP
oracle-database
nagendra singh
nagendra singh

Reputation: 43

Not a valid month error in oracle

SELECT c.CYCLE_OID,
SUM(CASE 
WHEN a.NAME = 'Loading' 
    THEN '1' 
ELSE '0' 
END) AS LOAD_COUNT from CYCLE c 
INNER JOIN mssolhist.CYCLEACTIVITYCOMPONENT a on a.OID = c.CYCLE_OID
WHERE c.STARTTIME_UTC >= to_date(to_char(sysdate-10,'dd-MM-yyyy')) AND     c.ENDTIME_UTC <= to_date(to_char(sysdate-5,'dd-MM-yyyy'))
GROUP BY c.CYCLE_OID 

ORA-01843: not a valid month

; nested exception is java.sql.SQLDataException: ORA-01843: not a valid month

I am getting a not valid month error in oracle when using this to_date expression. I have no idea why?

Upvotes: 1

Views: 1341

Answers (3)

anji
anji

Reputation: 21

instied of to_date(to_char(sysdate-10,'dd-MM-yyyy'),'dd-MM-yyyy')

to_date(to_char(sysdate-10,'dd-mon-yyyy'),'dd-mon-yyyy')

Upvotes: 2

Ravi Durairaj
Ravi Durairaj

Reputation: 831

Please use below query and it will work fine.

SELECT c.CYCLE_OID,
SUM(CASE 
WHEN a.NAME = 'Loading' 
    THEN '1' 
ELSE '0' 
END) AS LOAD_COUNT from CYCLE c 
INNER JOIN mssolhist.CYCLEACTIVITYCOMPONENT a on a.OID = c.CYCLE_OID
WHERE c.STARTTIME_UTC >= to_date(to_char(sysdate-10,'dd-MM-yyyy'),'dd-MM-yyyy') AND     c.ENDTIME_UTC <= to_date(to_char(sysdate-5,'dd-MM-yyyy'),'dd-MM-yyyy')
GROUP BY c.CYCLE_OID

Upvotes: 0

ntalbs
ntalbs

Reputation: 29438

It seems that there are two ways to avoid the error.

First, you are trying to convert date to string, then to date again. You don't need to do this. Just use date as it is.

WHERE c.STARTTIME_UTC >= sysdate-10 AND c.ENDTIME_UTC <= sysdate-5

If you want to truncate time part, then use trunc function.

WHERE c.STARTTIME_UTC >= trunc(sysdate-10) AND trunc(c.ENDTIME_UTC <= sysdate-5)

Or, if there's a special reason to use to_char, then you'd better to specify the date format when you try to convert string to date.

WHERE c.STARTTIME_UTC >= to_date(to_char(sysdate-10,'DD-MM-YYYY'), 'DD-MM-YYYY') 
  AND c.ENDTIME_UTC <= to_date(to_char(sysdate-5,'DD-MM-YYYY'), 'DD-MM-YYYY')

to_date function has optional fmt parameter. If you omit fmt, default format will be used. default format is determined implicitly by NLS_TERRITORY or can be set explicitly by NLS_DATE_FORMAT parameter.

Upvotes: 2

Related Questions