Solved

Date Comparison caswe statement - PL-SQL

Posted on 2014-12-16
16
262 Views
Last Modified: 2014-12-16
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.
0
Comment
Question by:saved4use
  • 8
  • 5
  • 3
16 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40503458
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40503466
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40503475
>>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
 
LVL 34

Expert Comment

by:johnsone
ID: 40503490
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40503499
>>So, it could go either way.

Agreed.
0
 

Author Comment

by:saved4use
ID: 40503522
Trunc is not working:
12:53:46 PM  ORA-01898: too many precision specifiers
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40503532
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40503534
Please post what you are using that is getting that message.  There were no precision specifiers in what was posted by slightwv.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:saved4use
ID: 40503543
@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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40503546
Are they DATE or VARCHAR2 columns?

If VARCHAR2 then you have bad data.
0
 

Author Comment

by:saved4use
ID: 40503551
The datatype is DATE.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40503556
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
 

Author Comment

by:saved4use
ID: 40503754
@slightwv:
This worked perfectly (thanks!):
 select  trunc(a.cls_dt), trunc(a.complete_dt) from some_table a;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40503761
>>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
 

Author Comment

by:saved4use
ID: 40503794
I added the case and everything worked perfect, i.e., no ORA-01898.
Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40503803
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
VB6 Compile Compatibility Issue 4 38
index Out OF Range Exception error 4 32
Clone Oracle 12c Database 5 27
Java Loop 4 22
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now