CODEWITHSUNDEEP
sqloracle-databasedate
A. Lemesanyi
A. Lemesanyi

Reputation: 37

how to compare date parts in SQL Oracle

I have a bit tricky question. E.g. I have a start_date: 15/01/2015 and an end date: 17/03/2015 for given record and I would like to generalize that to know, if the record with those exact start and end date belongs to January (by my definition even if it is 31/01/2015, then it belongs to January).

I did the following:

sum(case when to_date('2015/01/01','yyyy/mm/dd') between ROUND(dtime_method_start,'MONTH') and ROUND(dtime_method_end,'MONTH') then 1 else 0 end) as flag_jan

But the problem with Round function is, that it takes everything from 16-31 as next month, which is no good for me. How can I fix it or rewrite it to make it comply with my definition?

I want to know if a record with certain dtime_method_start and dtime_method_end belongs to January. I have many records with many different start and end dates and want to know how many of them belong to January.

Upvotes: 1

Views: 71

Answers (3)

David Aldridge
David Aldridge

Reputation: 52356

I would compare the stored dates directly to a range based on the input date, rather than applying a function to every date:

count(case when dtime_method_start >=            trunc(to_date('2015/01/01','yyyy/mm/dd'),'mm') 
            and dtime_method_start <  add_months(trunc(to_date('2015/01/01','yyyy/mm/dd'),'mm'),1)
           then 1
       end) as flag_jan

Or you could

count(case when trunc(dtime_method_start,'mm') = trunc(to_date('2015/01/01','yyyy/mm/dd'),'mm') 
           then 1
       end) as flag_jan

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

Just use trunc instead of round. Trunc with parameter 'MONTH' will truncate the date to the first day of month. If you test with between using first day of month it's ok.

Upvotes: 0

Kobi
Kobi

Reputation: 2524

 SELECT expected,
       CASE
           WHEN to_date('01/01/2015','DD/MM/YYYY') = ALL (trunc(start_date,'MONTH'), trunc(end_date,'MONTH'))
           THEN 1
           ELSE 0
       END flag_jan
FROM
  (SELECT 'notmatch 0'                        expected
         , to_date('15/01/2015','DD/MM/YYYY') start_date
         , to_date('17/03/2015','DD/MM/YYYY') end_date
   FROM dual
  UNION ALL 
   SELECT 'match 1'
        , to_date('12/01/2015','DD/MM/YYYY')
        , to_date('23/01/2015','DD/MM/YYYY')
   FROM dual
  ) dates;

this query compares the truncated start_date and end_date to match the first day of the month.

To check another month_flag, juste change the date in the first case expression.

Upvotes: 1

Related Questions