Comparison between dates in SQL using to

2018-06-12 22:36:39

TWO Dates have to be compared , one in format MM/DD/YYYY and another one in YYYY/MM/DD HH:MI:SS.

how can this be done. i tried using to_timestamp but it is throwing an error like :

  AND TO_TIMESTAMP(PAYMENT_DATE,'MM/DD/YYYY HH:MI:SS')>=TO_TIMESTAMP( :L_RESEND_DATE,'YYYY/MM/DD HH:MI:SS')

[Error] Execution (23: 27): ORA-01843: not a valid month

Since payment_date is already of type date you don't need to, and should not, be converting it. You are doing an implicit conversion from date to varchar2 based on your NLS settings, and then an explicit conversion back with your specified format model, and as a_horse_with_no_name said, that's both pointless and prone to error. So you're effectively doing:

TO_TIMESTAMP(TO_CHAR(PAYMENT_DATE, <NLS_DATE_FORMAT>),'MM/DD/YYYY HH:MI:SS')

If your session's NLS_DATE_FORMAT is DD/MM/YYYY for example, that means you're doing:

TO_TIMESTAMP(TO_CHAR(PAYMENT_DATE, 'DD/MM/YYYY'),'MM/DD/YYYY HH:MI:SS')

... and trying to swap the month and day number over, which would explain your error. As soon as you have a day number that is greater than 12 that double conversion will error, and other values will be incorrect even if no error is thrown.

Don't convert your table column at all, just convert the parameter:

AND PAYMENT_DATE >= TO_DATE(:L_RESEND_DATE, 'YYYY/MM/DD HH24:MI:SS')

I've used to_date() rather than to_timestamp() because that ends up matching the column's data type (and allows any index on that column to be used), and you don't need the fractional seconds precision that timestamps allow; I've also changed HH to HH24 since you don't have an AM/PM indicator.

This is now comparing a date with a date . It isn't relevant here as you don't need the extra precision, but if you did ever want to convert a date to a timestamp , you wouldn't need to have an intermediate varchar2 value, you could cast the value instead with CAST(PAYMENT_DATE AS TIMESTAMP) .

The following works perfectly:

SELECT 1 FROM DUAL
 WHERE TO_TIMESTAMP('11/23/2014 10:32:15','MM/DD/YYYY HH:MI:SS')
       >=
       TO_TIMESTAMP( '2013/11/29 12:54:12','YYYY/MM/DD HH:MI:SS');

So the solution is simple - most likely your Data is wrong... most likely some of your dates are DD/MM/YYYY or YYYY/DD/MM - If in doubt trust the error-message, which is very precise and clear - you have a Month-Value outside the range ;-)

Maybe try SELECT * FROM ... WHERE TO_NUMBER(SUBSTR(PAYMENT_DATE,1,2)) > 12; or otherwise do something similar with your bind-variable to find the dates which are wrong...

Oh and ALWAYS STORE DATES AS DATES OR IN ISO-FORM : XKCD

链接地址: http://www.djcxy.com/p/36954.html

上一篇: PHP Oracle将日期从yyyy转换为mm / dd / yyyy

下一篇: 在SQL中使用的日期之间的比较