WITH CODE AS ( SELECT '2013-08-09 12:11:23' AS A,TO_DATE('2013-08-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION SELECT '2013-09-09 12:11:23' AS A,TO_DATE('2013-09-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION SELECT '2013-10-09 12:11:23' AS A,TO_DATE('2013-10-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION SELECT '2013-11-09 12:11:23' AS A,TO_DATE('2013-11-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL )SELECT * FROM CODE WHERE TO_DATE(A, 'YYYY-MM-DD HH:MI:SS') >= TO_DATE('2013-09-01 12:00:00', 'YYYY-MM-DD HH:MI:SS');WITH CODE AS ( SELECT '2013-08-09 12:11:23' AS A,TO_DATE('2013-08-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION SELECT '2013-09-09 12:11:23' AS A,TO_DATE('2013-09-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION SELECT '2013-10-09 12:11:23' AS A,TO_DATE('2013-10-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION SELECT '2013-11-09 12:11:23' AS A,TO_DATE('2013-11-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL ) SELECT * FROM CODE WHERE B >= TO_DATE('2013-09-01 12:00:00', 'YYYY-MM-DD HH:MI:SS');
如果字段格式是时间SELECT * FROM warehouseoutdrug WHERE ACTDATE >= TO_DATE('2013-09-01 12:00:00', 'YYYY-MM-DD HH:MI:SS');查询成功。
VARCHAR2(32) 类型怎么能跟date类型比较呢to_date(ACT_DATE,'yyyy-mm-dd hh24:mi:ss') >= to_date('20130801010101' , 'yyyyMMddHHmmss')
SELECT '2013-08-09 12:11:23' AS A,TO_DATE('2013-08-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION
SELECT '2013-09-09 12:11:23' AS A,TO_DATE('2013-09-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION
SELECT '2013-10-09 12:11:23' AS A,TO_DATE('2013-10-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION
SELECT '2013-11-09 12:11:23' AS A,TO_DATE('2013-11-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL
)SELECT *
FROM CODE
WHERE TO_DATE(A, 'YYYY-MM-DD HH:MI:SS') >=
TO_DATE('2013-09-01 12:00:00', 'YYYY-MM-DD HH:MI:SS');WITH CODE AS (
SELECT '2013-08-09 12:11:23' AS A,TO_DATE('2013-08-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION
SELECT '2013-09-09 12:11:23' AS A,TO_DATE('2013-09-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION
SELECT '2013-10-09 12:11:23' AS A,TO_DATE('2013-10-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL UNION
SELECT '2013-11-09 12:11:23' AS A,TO_DATE('2013-11-09 12:11:23','yyyy-mm-dd hh:mi:ss') AS B FROM DUAL
)
SELECT *
FROM CODE
WHERE B >= TO_DATE('2013-09-01 12:00:00', 'YYYY-MM-DD HH:MI:SS');
FROM warehouseoutdrug
WHERE ACTDATE >= TO_DATE('2013-09-01 12:00:00', 'YYYY-MM-DD HH:MI:SS');查询成功。
时间格式错了
那么,直接比较就行
select * from cod_info where ACT_DATE >= '2013-08-01 01:01:01'
FROM warehouseoutdrug
WHERE ACTDATE >= TO_DATE('2013-09-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');