Solved

How do I used extract date in a case when statement

Posted on 2015-02-17
5
109 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
  • 3
  • 2
5 Comments
 
LVL 76

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 76

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 48

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 48

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 48

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to take different types of Oracle backups using RMAN.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

8 Experts available now in Live!

Get 1:1 Help Now