|
|
|
Forum Member
 
Group: Forum Members
Last Login: 9/23/2009 8:01:21 AM
Posts: 44,
Visits: 371
|
|
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 .
Regards,
Ansh
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/18/2009 5:48:26 AM
Posts: 273,
Visits: 1,214
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 9/23/2009 8:01:21 AM
Posts: 44,
Visits: 371
|
|
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 :-( ...
Regards,
Ansh
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/18/2009 5:48:26 AM
Posts: 273,
Visits: 1,214
|
|
|
Thanks! .... the priceless happiness & joy in such replies in the motivating factor to provide more answers! :)
|
|
|
|