Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure

Author

Topic

rafael.javellana
Starting Member

10 Posts

Posted - 2011-05-26 : 11:17:41

Hello Everyone,

Good day, I just want to ask regarding creating a stored procedure to get the data on a current month, 2 months ago and 3 months ago. The stored procedure will accept operationdate as a date variable and based on that date it will give the data based above.

Thanks in advance.

rfjavellana

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-26 : 11:21:16

Have a look at dateadd
dateadd(mm,datediff(mm,0,@d),0)
will give the start of the month input
You can use a common table expression to give the start and end dates of the months needed.
Use a between or datediff to allocate to the months.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-26 : 11:21:34

Hi,

try this in your select statement:

where date >= dateadd(month, -3, getdate())

-- where getDate() is todays date, -3 is 3 months of data in the past.. change to -2 for last two months -1 for last month

hope this helps

Where software development knowledge meets the reader

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-26 : 11:22:35

Is your question about how to write a store procedure or how to do date calculations or??

I'm guessting the date calculations.. do a search for DATEADD and DATEDIFF. If that doesn't help, take a look at this link to help form an actual question:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rafael.javellana
Starting Member

10 Posts

Posted - 2011-05-26 : 16:36:39

ok...thanks for the reply, I just want to ask what if I will get the month to date date?

rfjavellana

rafael.javellana
Starting Member

10 Posts

Posted - 2011-05-26 : 16:37:27

sorry, month to date data.

rfjavellana

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-26 : 16:38:36

where datediff(mm,dte,getdate()) = 0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rafael.javellana
Starting Member

10 Posts

Posted - 2011-05-26 : 16:48:01

ok...so dte is the variable? thanks

rfjavellana

rafael.javellana
Starting Member

10 Posts

Posted - 2011-05-26 : 16:53:27

for Lamprey, if you have the stored procedure much better for me...sorry I'm really a newbie to sql programming...

rfjavellana

rafael.javellana
Starting Member

10 Posts

Posted - 2011-05-26 : 17:07:28

guys, when I tried to use this code: where @Calldate > dateadd(month, -2, @Calldate) and replaced -2 to -3 for past 3 months I got the same data result.

rfjavellana

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources