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. Regards, C P Kulkarni On Mon, Jan 28, 2013 at 8:36 PM, Adrian Klaver <adrian.kla...@gmail.com>wrote: > 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: > > > http://www.postgresql.org/docs/9.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT > > -- > Adrian Klaver > adrian.kla...@gmail.com >