choudang Posted March 2, 2009 Share Posted March 2, 2009 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 More sharing options...
0 Antaris Veteran Posted March 2, 2009 Veteran Share Posted March 2, 2009 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 More sharing options...
0 choudang Posted March 2, 2009 Author Share Posted March 2, 2009 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 More sharing options...
Question
choudang
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