CODEWITHSUNDEEP
sqloracle-databasedate
Cool_Oracle
Cool_Oracle

Reputation: 313

Rounding off dates in oracle

I am working on dates and got the requirement in this way. In case the recruit date is between 1st - 7th day of the month (e.g. July 3 2014), then the recruit date should be July 1 2014. In case the recruit date is after the 7th day of the month (e.g. July 8, 2014. Recruit date should be on the following month Aug 1 2014. So, I prepared an SQL, using add months and round, but it is not giving me desired results. Could you please help me modify the query

SELECT AST.X_REC_DT,
CASE
WHEN (to_char(AST.X_REC_DT, 'DD') <= 7)
THEN ROUND(to_date(add_months(AST.X_REC_DT, -1)))
when (to_char(AST.X_REC_DT, 'DD') > 7)
then ROUND(to_date(add_months(AST.X_REC_DT, 1)))
END AS RTD
FROM EMPLOYEE AST WHERE AST.ROW_ID = '1-EWR'

Thanks.

Upvotes: 1

Views: 70

Answers (3)

Sentinel
Sentinel

Reputation: 6449

There's no need for any complicated case statements, just use this code:

select trunc(add_months(ast.x_rec_dt,1)-7,'mm') RTD
  from employee ast
 where ast.row_id = '1-EWR';

It first adds one month to the date in question, then subtracts 7 days from the new date, then truncates the whole thing to the beginning of the month.

Upvotes: 4

Gergely Bacso
Gergely Bacso

Reputation: 14651

This one works:

select recruit_date,
    case 
       when EXTRACT(day FROM recruit_date) <= 7 then trunc(recruit_date, 'MM')
       else ADD_MONTHS(trunc(recruit_date, 'MM'),1)
    end converted_date
from test;

It uses only three simple functions:

  • trunc (truncates date to day by default, but can be parametrized if needed as you see above)
  • add_months (adding months)
  • extract (extracting specifc date values)

Upvotes: 1

Matthew McPeak
Matthew McPeak

Reputation: 17924

Use a CASE statement to see if the date is on or before the 7th day of the current month. If it is not, add a month. This logic will show you how that works using all the dates in 2016 as test data.

WITH dates AS
       (SELECT TO_DATE ('01-JAN-2016') + ROWNUM - 1 dte
        FROM   DUAL
        CONNECT BY ROWNUM <= 365)
SELECT dte,
       -- This is the logic you want right here:
       CASE WHEN dte <= TRUNC (dte, 'MM') + 7 - 1 THEN TRUNC (dte, 'MM') ELSE TRUNC (ADD_MONTHS (dte, 1), 'MM') END
         modified_dte
FROM   dates;

Upvotes: 1

Related Questions