当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - date format in my oracle query

问题描述:

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,

fbl_dis d,

sp_offer o

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.

网友答案:

Change to

to_date(m.date1,'dd/mm/yyyy')

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/

分享给朋友:
您可能感兴趣的文章:
随机阅读: