• 0

Oracle query : get probable sunday and skip with +1 day


Question

All,

I need to automize the a report, where i am got stuck in oracle.

I have a database for customers with prep date whose bill has been generated on 5th and their last date of payment is 20th; but if 20th is Sunday, last date would be 21st.

i.e.

Cust_ID Prep_Date Due_Date

xxxxxxx 2-Mar-09 22-Mar-09

As 22nd is Sunday, due_date would be 23rd Mar

What would be the query / procedure

Regards/

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

You need to find out the day of the week of the projected 20th day of the month.

You can do that:

SELECT to_char(<date>, 'D') FROM dual;

I can't remember what Oracle considers the first day of the week, but I think generally sunday is 1, monday is 2 etc.

Can you base your logic around that.

1. Create date variable for 20th day of month.

2. Check to see if the day of week for the above date is 1. If so, add 1 day to the above date variable.

Something along the lines:

endDay DATE := '2009-03-20-00:00:00';
endDayWeek CHAR(1);
SELECT endDayWeek = to_char(endDay, 'D') FROM dual;
IF (endDayWeek = '1') THEN
	endDay = endDay + 1;
ENDIF;

Sorry if the syntax isnt quite right, I don't really do an Oracle.

Link to comment
Share on other sites

  • 0

thanks, i will try.

by the way, it become more complicated when there will be 5 cycles. as in telecom, we are having 8 bill cycles, i.e. 2, 9, 15, 20, 24 and 27. so, whenever i will append the new billed base, i need to also calculate the cut off day also i.e. 20th day. so i can not take manual calculation of dates, as there is a possibility for skip of prep_date.

so, first i need to check the 20th day of whether it is 1 (sunday) or not, if 1 the +1.... so i need to run a update query along with the append query.

Will post the query after giving a try

Regards/

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.