I know that. I have to check the period (dates entered by user) must be correct and must be perfectly divisible by the interval given. This is a pre-check for the interest calculation.
If user enters '01/04/2010' and '15/05/2010' as the dates, and interval as 'month' then, there are 15 days left and if the banking product is set to calculate interest for a complete month only, then calculation can result in wrong interest figures. So I have to check if given period is perfectly divisible by the interval or not.
On 01/28/2013 05:24 AM, c k wrote: > Hi, > I have two variables in pl/pgsql function. > p_fromdate and p_todate > > I have another variable which represents intervals like day, month, > quarter etc. > p_interval as smallint, to hold values like 1,2,3, which are > substituted for intervals as '1 day', '1 month - 1 day', '3 months - 1 > day' respectively. > Now, I have to find if the period of given two dates (p_todate - > p_fromdate) is multiples of the given interval or not? > > e.g. p_fromdate = '01/04/2010'; > p_todate = '31/03/2013'; > > p_interval=3 (which is a quarter). > > I need to find out if the period of ('31/03/2013' - '01/04/2010') > clearly multiple of a quarter and modulus = 0. > Important point is user can enter any dates and choose any interval to > check. 'Day' interval fits to any dates. For 'month' and others, number > of days, minutes, seconds are varying. So we can not use the fixed > values for them neither we can use '1 month - 1 day' or any interval in > division. Also we can not cast them to integers. > > How to get it done?
Not quite sure what you are trying to accomplish. Have you looked at EXTRACT, it seems to cover some of what you describe:
