saved4use
asked on
Date Comparison caswe statement - PL-SQL
The case statement below seems to work when all the dates are 2014, yet when it is 2013 or 2015, it shows a 0 when it actually should be 1.
How can I modify it to reflect the correct result? For example, when the complete_dt = 12/15/2014 and the cls_dt = 1/2/2015, then it should reflect 1, NOT 0.
case when to_char(a.cls_dt,'mm/dd/yy yy') >= to_char(a.complete_dt,'mm/ dd/yyyy')
then '1' else '0' end as met_cls_ind
Thank you for your tremendous assistance.
How can I modify it to reflect the correct result? For example, when the complete_dt = 12/15/2014 and the cls_dt = 1/2/2015, then it should reflect 1, NOT 0.
case when to_char(a.cls_dt,'mm/dd/yy
then '1' else '0' end as met_cls_ind
Thank you for your tremendous assistance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>case when a.cls_dt >= a.complete_dt
Likely need the trunc or better date checks because of the times.
This would return 0 but bet it should be 1.
1/1/2014 13:01:01 >= 1/1/2014 14:01:01
Since it is in a case statement, shouldn't have to worry about index usage so trunc is simpler.
Likely need the trunc or better date checks because of the times.
This would return 0 but bet it should be 1.
1/1/2014 13:01:01 >= 1/1/2014 14:01:01
Since it is in a case statement, shouldn't have to worry about index usage so trunc is simpler.
I realized that after I hit submit. However, I also see where people ignore the time when they shouldn't. So, it could go either way.
>>So, it could go either way.
Agreed.
Agreed.
ASKER
Trunc is not working:
12:53:46 PM ORA-01898: too many precision specifiers
12:53:46 PM ORA-01898: too many precision specifiers
Are a.cls_dt and a.complete_dt date data types or varchar2?
If varchar2 try:
case when to_date(a.cls_dt,'mm/dd/yy yy') >= to_date(a.complete_dt,'mm/ dd/yyyy')
then '1' else '0' end as met_cls_ind
If varchar2 try:
case when to_date(a.cls_dt,'mm/dd/yy
then '1' else '0' end as met_cls_ind
Please post what you are using that is getting that message. There were no precision specifiers in what was posted by slightwv.
ASKER
@slightwv
I get this error prompt from your to_Date suggestion:
ORA-01858: a non-numeric character was found where a numeric was expected
I get this error prompt from your to_Date suggestion:
ORA-01858: a non-numeric character was found where a numeric was expected
Are they DATE or VARCHAR2 columns?
If VARCHAR2 then you have bad data.
If VARCHAR2 then you have bad data.
ASKER
The datatype is DATE.
That explains the ORA-01858. I said to try that if it was a varchar2.
If it is a date, you shouldn't be getting the "ORA-01898: too many precision specifiers" in what I posted.
Just try selecting the tunc'ed dates by themselves and nothing else:
select trunc(a.cls_dt), trunc(a.complete_dt) from some_table a;
If it is a date, you shouldn't be getting the "ORA-01898: too many precision specifiers" in what I posted.
Just try selecting the tunc'ed dates by themselves and nothing else:
select trunc(a.cls_dt), trunc(a.complete_dt) from some_table a;
ASKER
@slightwv:
This worked perfectly (thanks!):
select trunc(a.cls_dt), trunc(a.complete_dt) from some_table a;
This worked perfectly (thanks!):
select trunc(a.cls_dt), trunc(a.complete_dt) from some_table a;
>>This worked perfectly
OK, now add the case:
select case when trunc(a.cls_dt) >= trunc(a.complete_dt)
then '1' else '0' end as met_cls_ind from some_table a;
What I'm trying to do is narrow down exactly what is causing the ORA-01898.
As johnsone also suggested, I think it is somewhere else in the query.
OK, now add the case:
select case when trunc(a.cls_dt) >= trunc(a.complete_dt)
then '1' else '0' end as met_cls_ind from some_table a;
What I'm trying to do is narrow down exactly what is causing the ORA-01898.
As johnsone also suggested, I think it is somewhere else in the query.
ASKER
I added the case and everything worked perfect, i.e., no ORA-01898.
Thanks.
Thanks.
Then the error has to be somewhere else in the query.
If the case statement you originally posted runs in the complete query, I don't see how making the change to my case statement with the trunc calls will generate an error. The last query that you ran with just the case statement seems to support that.
Make sure all you changed from the SQL you had when you posted the question until now was just the one case statement.
Even though we don't have your tables, if you can post the entire query we might be able to offer advice on where to look.
If the case statement you originally posted runs in the complete query, I don't see how making the change to my case statement with the trunc calls will generate an error. The last query that you ran with just the case statement seems to support that.
Make sure all you changed from the SQL you had when you posted the question until now was just the one case statement.
Even though we don't have your tables, if you can post the entire query we might be able to offer advice on where to look.
case when a.cls_dt >= a.complete_dt
then '1' else '0' end as met_cls_ind
Also, you are doing a string comparison. To effectively do that with dates, the format would need to be yyyy/mm/dd.