Difference in days between 2 dates Oracle SQL

问题:

Alright, so I've checked many many many other posts on stackoverflow to see if this is mentioned anywhere, and the answers provided don't quite make sense to me...I'm thinking they're talking about something completely different.

Here's what I want to do, using Oracle SQL Developer:

-Retrieve entries from purch_date

-Get the difference IN DAYS between the purch_date and Christmas of the CURRENT YEAR

**So, therefore '2012' can't be hard-coded in there. I need to retrieve it.

Here's the query that works 100% in MySQL:

SELECT purch_id AS PURCH_ID, 
DATEDIFF(CONCAT(YEAR(NOW()),'-12-25'), purch_date) AS DAYS_TO_CHRISTMAS
FROM CS260USER.candy_purchase;

Pretty much, I need that to work in Oracle.

Can anyone help me with this?

Many many thanks!

-Matthew

回答1:

Assuming that purch_date is defined as a DATE

SELECT purch_id,
       (trunc(sysdate, 'YYYY') +
         interval '11' month +
         interval '24' day) -
        purch_date days_to_christmas
  FROM CS260USER.candy_purchase;

A couple of notes

1. If purch_date has a time component, you might want to truncate the result of the date subtraction.
2. If this query is executed between December 26 and December 31, you'll still be looking at this year's Chrismas (i.e. a date in the past) rather than next year's Christmas. I would thing that if purch_date was, for example, December 30, that you would want the result to be 360 days until the next Christmas rather than -5 days until the last Christmas.

回答2:

The easeyest way of doing this is writing a function in oracle like DATEDIFF in mysql, id done this like :

function        datediff( p_what in varchar2, p_d1 in date, p_d2 in date) return number as  l_result    number; 
BEGIN
      select (p_d2-p_d1) * 
             decode( upper(p_what), 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) 
       into l_result from dual; 

      return l_result; 

END;

and call this function like :

DATEDIFF('YYYY-MM-DD', SYSTIMESTAMP, SYSTIMESTAMP)

in your sql command