• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

How do I used extract date in a case when statement

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
dfn48
Asked:
dfn48
  • 3
  • 2
1 Solution
 
arnoldCommented:
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
 
arnoldCommented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now