CODEWITHSUNDEEP
sqloracle11g
Basit
Basit

Reputation: 8626

How to check date lies in the month from JAN to JUNE and JULY to DECEMBER

I have a date. I want to check whether the date lies between JAN to JUNE or JULY to DECEMBER. Like suppose if user enter input date 28-NOV-12 then how can i check that this date lies between JULY to DECEMBER ? and if user input date 28-FEB-12 then this date lies between JAN to JUNE? Basically i want to check dates on half yearly basis ?

Thanks

Upvotes: 1

Views: 3150

Answers (4)

René Nyffenegger
René Nyffenegger

Reputation: 40543

select sign(2.5-to_char(<date>, 'q')) from dual;

returns 1 for dates between January 1st and June 30th and -1 otherwise.

Explanation:

to_char(date, 'q') returns the Quarter of year (1, 2, 3, 4; January - March = 1) of date (see format models). For November 28th, that would be 4.

2.5-quarter returns a negative number for quarter 3 and 4, and a positive number for quarter 1 and 2, respectively. The sign reduces the negative and positive numbers to a simpler -1 and 1.

Testcase:

with da as (
  select date '2012-01-01' te from dual union all
  select date '2012-02-02' te from dual union all
  select date '2012-03-03' te from dual union all
  select date '2012-04-04' te from dual union all
  select date '2012-05-05' te from dual union all
  select date '2012-06-06' te from dual union all
  select date '2012-07-07' te from dual union all
  select date '2012-08-08' te from dual union all
  select date '2012-09-09' te from dual union all
  select date '2012-10-10' te from dual union all
  select date '2012-11-11' te from dual union all
  select date '2012-12-12' te from dual
)
select 
  da.te,
  decode (sign(2.5-to_char(da.te, 'q')),
           1, 'Jan-Jun',
          -1, 'Jul-Dec')
from da;

Upvotes: 1

Viru
Viru

Reputation: 535

Try this ,date_column should be date datatype other wise change to TO_DATE(date_column)

select
case
    when date_column BETWEEN  TO_DATE('01-JAN-12') AND TO_DATE('03-JUN-12')then '1st Half' 
    ELSE '2nd Half'
end as DatePosition
from table_name

Upvotes: 0

Michał Powaga
Michał Powaga

Reputation: 23183

Try to_char function, e.g. this way:

select
    case
        when to_char(date_column, 'mm') <= 6 then '1st' 
        when to_char(date_column, 'mm') >= 7 then '2nd'
    end as half_of_the_year
from your_table

to_char(date_column, 'mm') returns month (01..12, 01 - January) part of the date.

select
    case
        when to_char(sysdate, 'mm') <= 6 then '1st' 
        when to_char(sysdate, 'mm') >= 7 then '2nd'
    end as half_of_the_year
from dual

today returns:

HALF_OF_THE_YEAR
2nd

Upvotes: 0

Murali N
Murali N

Reputation: 3498

select * from table "28-NOV-2012" WHERE date_column  between TO_DATE('01-JAN-2012') AND TO_DATE('01-JUN-2012');

Upvotes: 0

Related Questions