Solved

How do I used extract date in a case when statement

Posted on 2015-02-17
5
117 Views
Last Modified: 2015-02-17
I am getting an error in my code when I used the extract function.   What the below code does is:  subtract  date1 from date2,   and when the subtracted dates are between 1 and 30 then UTL_AMOUNT ELSE 0
and then subtract  date3 from date2 when the subtracted dates are between 1 and 30 then   payment_30day_due_amt ELSE 0.
  date1 - date3 are date timestamp
 
The values of date1-date3 are as follows:
 date1: 2015-01-03 00:00:00
 date2: 2014-12-14 00:00:00
 date3: 2015-02-17 00:00:00

The values of amounts are as follows:
UTL_AMOUNT = 10000
payment_30day_due_amt=2345

Here is my code:
case when (extract(Month From cl.date1 - extract(Month From cl.date2))  between 1 and 30) then UTL_AMOUNT
ELSE 0
case when (extract(Month From cl.date3 - extract(Month From cl.date2))  between 1 and 30) then payment_30day_due_amt
ELSE 0
end as mat_amt
0
Comment
Question by:dfn48
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 40615614
Is t his oracle or ms SQL ?

For mssql you can use datediff(d,startdate,end date) returns the number if days.

Though I am uncertain I understand what you're looking to do.

Not sure what extract does, but it seems you are extracting a month while
Between 1,30 might mean months versus days.

You need to subtract the dates if a direct function does not exist, converting it to a more manageable I.e to a Unix time since epoch which would let you do a direct substraction  of the values and then converting it to the value you wish to get.

I'll check on what extract does and whether oracle has abetted suited date function exists equivalent to the datediff()
0
 
LVL 78

Expert Comment

by:arnold
ID: 40615618
Oracle has the same datediff with the DP_day to designate that you are interested in the difference report in days.

Here is a link that might help.

http://docs.oracle.com/cd/E10530_01/doc/epm.931/html_esb_techref/maxl/dml/funcs/datediff.htm
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40615632
That's a "Oracle’s Essbase® Technical Reference".

The Oracle rdbms doesn't use (or need) datediff()

What I want to know is are the fields of type: date, or timestamp?
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40615651
if of type timestamp try this
 CASE 
     WHEN ( extract(Month FROM cl.date1 - extract(Month FROM cl.date2)) BETWEEN 1 AND 30 )
            THEN 1
     WHEN ( extract(Month FROM cl.date3 - extract(Month FROM cl.date2)) BETWEEN 1 AND 30 )
           THEN 2
     ELSE 0
  END AS mat_amt

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40615702
sorry there is more. above just suggests a case expression structure that should work.

I suspect this is a 30 DAY period you are evaluating, so this may work:
 CASE 
     WHEN ( to_date(to_char(date1,'yyyy-mm-dd'),'yyyy-mm-dd') - to_date(to_char(date2,'yyyy-mm-dd'),'yyyy-mm-dd') BETWEEN 1 AND 30 )
            THEN UTL_AMOUNT
     WHEN ( to_date(to_char(date3,'yyyy-mm-dd'),'yyyy-mm-dd') - to_date(to_char(date2,'yyyy-mm-dd'),'yyyy-mm-dd') BETWEEN 1 AND 30 )
            THEN payment_30day_due_amt
     ELSE 0
  END AS mat_amt

Open in new window

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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