CODEWITHSUNDEEP
sqlsql-server
Serenity
Serenity

Reputation: 11

SQL - Converting MONTH extract from number to full month name

Having trouble with a particular stored procedure. I think I have it mostly correct... I basically want to be able to search for a month and year and have any products' rows sold within that timeline show up.

CREATE PROCEDURE sp_products
    @productname    CHAR(20),
    @month      CHAR(9),
    @year           CHAR(4)


AS
SELECT  p.ProductName, 
        p.UnitPrice, 
        p.UnitsInStock,
        s.Name

FROM        Orders o
INNER JOIN  OrderDetails od
ON          o.OrderID = od.OrderID
INNER JOIN  Products p
ON          od.ProductID = p.ProductID
INNER JOIN  Suppliers s
ON          p.SupplierID = s.SupplierID 

WHERE   p.ProductName = @productname
AND     MONTH(o.OrderDate) = @month
AND     YEAR(o.OrderDate) = @year

Giving me a "Conversion failed when converting the varchar value 'December ' to data type int." error.

Here's the query it needs to work with:

EXEC sp_products '%tofu%', 'December', 1992

I think I know what I need to do, just don't know how to do it...
Help!

Upvotes: 1

Views: 2069

Answers (4)

Andriy M
Andriy M

Reputation: 77737

The following two conditions are probably making your query non-sargable, because in each case you are applying a function to a column being filtered on:

AND     MONTH(o.OrderDate) = @month
AND     YEAR(o.OrderDate) = @year

I would change them like this:

SELECT     …
           …
           …

FROM       Orders o

/* this join actually replaces those two filters */
INNER JOIN (SELECT CAST('1-' + @month + CAST(-@year AS varchar(5))) AS Month) d
ON         o.OrderDate >= d.Month AND o.OrderDate < DATEADD(MONTH, 1, d.Month)

INNER JOIN … /* the rest of your joins */

WHERE      p.ProductName = @productname  /* only one condition here now */

That is, the @year and @month arguments are formatted as a string convertible to a datetime value representing the first of the corresponding month, then you are besically filtering using this logic:

order date >= the first of the given month
and
order date < the first of the next month after the given

And of course, as you've already been told, the @productname filter in your query should probably use the LIKE comparison instead of the =, because it seems like you want to search by a mask as well as by a specific name. So the WHERE clause would then look like this:

WHERE      p.ProductName LIKE @productname

Upvotes: 1

jlnorsworthy
jlnorsworthy

Reputation: 3974

MONTH() returns an int.

Instead of MONTH(o.OrderDate), try DATENAME(m, o.OrderDate)

EDIT:

Also note that your input will not give you results are you are using wildcards without using the LIKE operator.

Update your procedure with this line:

WHERE p.ProductName LIKE @productname

Upvotes: 1

Sathya Narayanan
Sathya Narayanan

Reputation: 847

As answered by jlnorsworthy

"Instead of MONTH(o.OrderDate), try DATENAME(m, o.OrderDate)"

This will work

For no result pls check what the input you are passing

EXEC sp_products '%tofu%', 'December', 1992

you are comparing it with = operator

WHERE   p.ProductName = @productname

change = to like it will work.

Upvotes: 0

hkf
hkf

Reputation: 4520

To convert a month string to a 1-12 representation of a month, use the inbuilt function DATENAME - reconstruct your date with something like, 01 December 1922, then use that for the input into DATENAME

Upvotes: 0

Related Questions