Solved

How do I used extract date in a case when statement

Posted on 2015-02-17
5
112 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 77

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 77

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

776 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