用于sysdate比较的Varchar2

Question

我正在查询的表的日期存储为varchar2。我需要在sysdate-1和sysdate-30之间使用比较存储为varchar2和sysdate的日期（以返回上个月的varchar2日期）。

当指定to_date(varchar2,'dd-mon-yyyy')时，我得到错误“literal不匹配格式字符串”。

Oracle文档说这是to_date()从varchar2转换时可以接受的格式，这让我感到困惑。

更新：这是一个公司数据库，我没有设计数据库，只能使用我现有的。数据集是巨大的，由SCADA设备自动更新，每天超过10,000个设备。

SELECT device_name, read_date, sysdate

FROM oracle_database

------- Data Returned by query --------

device_name read_date   sysdate
Device 1    5/14/2013   22-Sep-14
Device 2    5/14/2013   22-Sep-14
Device 3    5/14/2013   22-Sep-14
Device 4    5/14/2013   22-Sep-14
Device 5    5/14/2013   22-Sep-14
Device 6    5/14/2013   22-Sep-14
Device 7    5/14/2013   22-Sep-14

使用TO_DATE的结果

ORA-01861:文字与格式字符串不匹配




Answer 1

根据您的数据，正确的格式很可能是mm/dd/yyyy。它将匹配单位数和两位数的月或天。

• mm:月份从01到12（前导0默认情况下不是强制性的）
• \:任何标点符号
• dd:日期从01到31（前导0默认情况下不是强制性的）
• \:任何标点符号
• yyyy:年

有关详细信息，请参阅Oracle关于datetime格式模型的文档。

-- Some test data
WITH testdata AS (
  SELECT '5/14/2013' as d FROM DUAL
  UNION SELECT '05/14/2013' FROM DUAL
  UNION SELECT '5/1/2013' FROM DUAL
  UNION SELECT '5/01/2013' FROM DUAL
  UNION SELECT '6-6-2013' FROM DUAL)

-- Actual query demonstrating the use of the MM/DD/YYYY format
select d, TO_DATE(d,'MM/DD/YYYY') FROM testdata

D           TO_DATE(D,'MM/DD/YYYY')
05/14/2013  05/14/2013
5/01/2013   05/01/2013
5/1/2013    05/01/2013
5/14/2013   05/14/2013
6-6-2013    06/06/2013

• fx标志强制严格比较标点符号和格式
中的位数
• fm标志放松比较，允许最多指定位数