All Forums Database
Ansh 49 posts Joined 10/08
02 Jul 2009
Urgent Need

Hi All,I have a requirement in which I am passing two parameters Month_Start and Month_End and I need to fetch the data for the previous month based on that.I have used Add_months function to get this but there is a problem when months are less than 31 days.I tried the below query :Sel * from tablename where Date_Columnname >= add_months(: Month_Start,-1) and Date_Columnname <= add_months(:Month_End,-1);If I use Month_Start and Month_End as for example '2009-02-01' and '2009-02-28' respectively,the query will only fetch the the values of Date_Columnname from '2009-01-01' till '2009-01-28' excluding dates '2009-01-29' till '2009-01-31'I need to correct this so that I get the complete previous month data.How can we accomplish this in teradata.Thanks for a quick reply .

Adeel Chaudhry 773 posts Joined 04/08
02 Jul 2009

Hello,How about only using 1 parameter with following query:SELECT * FROM Table1 WHERE DateColumn1 >= ADD_MONTHS(:Month_Start,-1) AND DateColumn1 < Month_Start;Or, you can do following in the Stored-Procedure/Macro (whatever you are using):- Get the input date (only start would do)- Extract MONTH & YEAR from input date- Create a variable "Var1" as '01-' || (MONTH - 1) || YEAR- Create another variable "Var2" as '01-' || MONTH || YEAR- Then use both variables in following query:SELECT * FROM Table1 WHERE DateColumn1 >= Var1 AND DateColumn1 < Var2;That way, you don't need to worry about month ending on 28, 29, 30 or 31 .... because as a matter of fact every month has 1st day and day less than next month's 1st day is last day of previous month! :)HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Ansh 49 posts Joined 10/08
02 Jul 2009

Thanks a million Adeel...U really are a supreme being of this forum :-) ....I dont know why it dint clicked my mind to remove Month_End :-( ...

Adeel Chaudhry 773 posts Joined 04/08
02 Jul 2009

Thanks! .... the priceless happiness & joy in such replies in the motivating factor to provide more answers! :)

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.