SQL PROCEDURS - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL PROCEDURS

Author

Topic

raabhim
Starting Member

2 Posts

Posted - 2012-07-02 : 17:54:37

Hi there,
Im trying to create a stored procedure with the variables
@month, @year

I don't know how to really use the veriables with the datetime data type. here's a part of my code


CREATE PROCEDURE sp_product_listing 
(
  @month   varchar(10),  -- i'm not sure which datatype i need to use
  @year    varchar(4)
)
AS
SELECT.... -- omitted
FROM orders o
WHERE o.order_date BETWEEN @month+'1'+@year AND @month+'30'+@year

Thank you for your help!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-07-02 : 18:28:12

CREATE PROCEDURE sp_product_listing
(
@month tinyint, -- i'm not sure which datatype i need to use
@year smallint
)
AS

DECLARE @MonthStart datetime (or date if on sql 2008 or higher)
DECLARE @MonthEnd datetime
SET @MonthStart = DATEADD(Month,@month-1,DATEADD(year,@year - 1900,0))
SET @MonthEnd = DATEADD(month,1,@MonthStart)

SELECT.... -- omitted
FROM orders o
WHERE o.order_date >= @MonthStart and o.orderdate < @monthEnd

Jim

Everyday I learn something that somebody else already knew

raabhim
Starting Member

2 Posts

Posted - 2012-07-02 : 19:52:56

Works like a charm!

Thank you my friend!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-07-02 : 19:59:57

You're welcome.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources