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.
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! :)