Urgent Need

Database
Enthusiast

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 .

3 REPLIES
Teradata Employee

Re: Urgent Need

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
Enthusiast

Re: Urgent Need

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 :-( ...
Teradata Employee

Re: Urgent Need

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