I am trying to run following select query and its throwing error for date1 as invalid month:
SELECT rpad(REPLACE(DECODE(SUBSTR(m.alt_ch_rsp,1,3),'ABM','ATM','SOL','MC ','BRN','FFT','CC '),' ', NULL ),3, ' ')
FROM sp_mosaix m,
WHERE o.offer_id = m.offer_id
AND (TRUNC(to_date(m.date1,'yyyy/mm/dd')) < TRUNC(sysdate+10))
the date1 is actually a varchar2 data type and NLS_DATE_FORMAT for my database is "DD-MON-RR". I wonder how could I rectify and apply proper date conversion so that it doesn't throw date1 error.
The pattern you use for to_date should match the format of the text - no matter what your NLS_DATE_FORMAT says.
Your formatting needs to correspond with varchar2 data, 'MM/DD/YYYY'. Since your date1 data follows the format, '09/11/2014', this date model corresponds to 'MM/DD/YYYY'.
ALTER session SET NLS_DATE_FORMAT='DD-MON-RR'; [email protected]> WITH m AS 2 (SELECT '09/11/2014' date1 FROM dual 3 ) 4 SELECT date1 5 FROM m 6 WHERE TRUNC(to_date(m.date1,'MM/DD/YYYY')) < TRUNC(sysdate+10) 7 / DATE1 ========== 09/11/2014
The actual issue is with your DATA TYPE. A date column MUST always be a DATE data type. There is no reason for it to be defined as VARCHAR2.
It is not only a bad practice, but, down the line, you will face the consequences and side-effects. It is most basic tuning concept in nature, but also very important. And not to forget the performance issues due to implicit data conversion.
As a good reading, you can look at the following article by Ed Stevens, whom I trust as a good DBA : http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/