CODEWITHSUNDEEP
sqloracle-database
Titus Antony
Titus Antony

Reputation: 19

Closest Date to a given date - SQL Oracle

Oracle (SQL) - I have 3 available dates in a month (1st, 10th and 25th). I need a query to find out the closest among the 3 dates based on the date of executing my query. For e.g, when i run the query on 4th, i should get 10th as my result, when i run on 12th, the result should be 25th and when i run on 27th, the result should be the 01st of next month. I am struggling with the logic. Please help..

Upvotes: 0

Views: 3212

Answers (4)

Brian Leach
Brian Leach

Reputation: 2101

I believe this is much more efficient and simpler than the other solutions. 

WITH
    possible_dates
    AS
        -- generate the three available dates for the current month
        (SELECT TRUNC (SYSDATE, 'MM') available_date
           FROM DUAL
         UNION ALL
         SELECT TRUNC (SYSDATE, 'MM') + 9
           FROM DUAL
         UNION ALL
         SELECT TRUNC (SYSDATE, 'MM') + 24
           FROM DUAL
         UNION ALL
         SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1)
           FROM DUAL),
    delta
    AS
        -- calculate the distance of those available dates
        (SELECT (available_date - SYSDATE) diff, available_date
           FROM possible_dates)
SELECT *
  FROM delta
 WHERE diff = (SELECT MIN (diff)
                 FROM delta
                WHERE diff >= 0);

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

WITH mytable(dt) AS
  (SELECT '01'
   FROM dual
   UNION ALL SELECT '10'
   FROM dual
   UNION ALL SELECT '25'
   FROM dual),
     given_dates AS
  (SELECT Trunc (To_date (dt || To_char(sysdate, 'MMYYYY'), 'DDMMYYYY')) dt,
          Trunc(sysdate) cdate
   FROM mytable),
     comp AS
  (SELECT cdate,
          CASE
              WHEN ABS (cdate - dt) < ABS (cdate - Add_months (dt, 1)) THEN dt
              ELSE Add_months (dt, 1)
          END dt_comp
   FROM given_dates)
SELECT dt_comp closest_date
FROM
  (SELECT dt_comp,
          rank() OVER (
                       ORDER BY ABS (cdate - dt_comp)) rn
   FROM comp)
WHERE rn = 1;

Upvotes: 0

Gurvinder Singh
Gurvinder Singh

Reputation: 111

If using PL SQL is an option, then use the query as following: 

`DECLARE
   curr_month        CHAR(2);
   curr_year         CHAR(4);
   future_date       DATE;
BEGIN
   select to_char(sysdate, 'MM') INTO curr_month from dual;
   select to_char(sysdate, 'YYYY') INTO curr_year from dual;
   future_date := TO_DATE('12' ||  curr_month || curr_year, 'DD/MM/YYYY');
   IF (SYSDATE > future_date) THEN
        {..whatever you want to do...}
   ELSIF (SYSDATE > future_date2) THEN
        {..whatever you want to do...}
   END IF;
END;`

Upvotes: 0

user5683823
user5683823

Reputation: 

with
     inputs ( dt ) as (
       select to_date( '03/24/2015 11:30:00', 'mm/dd/yyyy hh24:mi:ss') from dual union all
       select to_date( '08/03/2016 07:15:00', 'mm/dd/yyyy hh24:mi:ss') from dual union all
       select to_date( '02/29/2016 22:30:00', 'mm/dd/yyyy hh24:mi:ss') from dual
     )
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select dt,
       case when extract(day from dt) < 10 then trunc(dt, 'mm') + interval  '9' day
            when extract(day from dt) < 25 then trunc(dt, 'mm') + interval '24' day
            else add_months(trunc(dt, 'mm'), 1)
       end  as next_std_dt
from   inputs;

DT                   NEXT_STD_DT       
-------------------  -------------------
03/24/2015 11:30:00  03/25/2015 00:00:00
08/03/2016 07:15:00  08/10/2016 00:00:00
02/29/2016 22:30:00  03/01/2016 00:00:00

Upvotes: 1

Related Questions