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/yyyy') >= to_char(a.complete_dt,'mm/dd/yyyy')
then '1' else '0' end as met_cls_ind


Thank you for your tremendous assistance.
saved4useAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
to_char on a date makes it a string/ascii compare.

Try:
case when trunc(a.cls_dt) >= trunc(a.complete_dt)
 then '1' else '0' end as met_cls_ind
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Why the character conversion?

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.
0
slightwv (䄆 Netminder) Commented:
>>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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnsoneSenior Oracle DBACommented:
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.
0
slightwv (䄆 Netminder) Commented:
>>So, it could go either way.

Agreed.
0
saved4useAuthor Commented:
Trunc is not working:
12:53:46 PM  ORA-01898: too many precision specifiers
0
slightwv (䄆 Netminder) Commented:
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/yyyy') >= to_date(a.complete_dt,'mm/dd/yyyy')
 then '1' else '0' end as met_cls_ind
0
johnsoneSenior Oracle DBACommented:
Please post what you are using that is getting that message.  There were no precision specifiers in what was posted by slightwv.
0
saved4useAuthor Commented:
@slightwv
I get this error prompt from your to_Date suggestion:

 ORA-01858: a non-numeric character was found where a numeric was expected
0
slightwv (䄆 Netminder) Commented:
Are they DATE or VARCHAR2 columns?

If VARCHAR2 then you have bad data.
0
saved4useAuthor Commented:
The datatype is DATE.
0
slightwv (䄆 Netminder) Commented:
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;
0
saved4useAuthor Commented:
@slightwv:
This worked perfectly (thanks!):
 select  trunc(a.cls_dt), trunc(a.complete_dt) from some_table a;
0
slightwv (䄆 Netminder) Commented:
>>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.
0
saved4useAuthor Commented:
I added the case and everything worked perfect, i.e., no ORA-01898.
Thanks.
0
slightwv (䄆 Netminder) Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.