CODEWITHSUNDEEP
sqlsql-server
DeE DEe
DeE DEe

Reputation: 335

Extract month number?

I have a procedure where user inputs month e.g 'JAN' to extract data for that month. I want to compare the input month with month part in a date. This is what I got so far.

CREATE PROCEDURE SavingsAccountsAllDetail
(@Yr INT=NULL,
@Mon CHAR(3)=NULL)

AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Year INT
    DECLARE @Month CHAR(3)

    SET @Year = @Yr
    SET @Month = @Mon

and comparing part.

datepart(year, A.DateOpen)=@Year AND datepart(month, A.DateOpen)=datepart(MM, @Month)

But when I execute my procedure it says

Conversion failed when converting date and/or time from character string. Severity 16

Any idea on how to proceed?

Upvotes: 0

Views: 106

Answers (5)

steenbergh
steenbergh

Reputation: 1761

Your @Year and @Month variables are not dates. They are string representations of parts of a date.

When you extract the dateparts from A.DateOpen you can directly compare that to those variables:

datepart(year, A.DateOpen)=@Year AND datepart(month, A.DateOpen)=@Month

However, datepart will return a numerical value and you want to compare to text (eg 'JAN'). Consider adding the 'DateName()' function, and changing the line to:

datepart(year, A.DateOpen)=@Year AND left(datename(month, A.DateOpen), 3)=@Month

Upvotes: 1

Praveen
Praveen

Reputation: 9335

you are getting the error from 

datepart(month, A.DateOpen)=datepart(MM, @Month)

as @Month can have the value Jan, Feb etc.. which sql server couldn't convert to a proper date.

Assign proper value to @month from @Mon like;

DECLARE @Month int
select @Month = 
    case @Mon
    when 'Jan' then 1  
    when 'Feb' then 2  
    when 'Mar' then 3  
    when 'Apr' then 4  
    when 'May' then 5  
    when 'Jun' then 6  
    when 'Jul' then 7  
    when 'Aug' then 8  
    when 'Sep' then 9  
    when 'Oct' then 10  
    when 'Nov' then 11  
    when 'Dec' then 12 
    else  0 
end

and use @month in the query like;

datepart(year, A.DateOpen) = @Year AND datepart(month, A.DateOpen) = @Month

Upvotes: 0

Chanom First
Chanom First

Reputation: 1136

please try this like query 

DECLARE @inpMonth CHAR(3) = 'SEP'
SELECT CASE WHEN @inpMonth = FORMAT(GETDATE(),'MMM') THEN 1 ELSE 0 END AS [res]

I use in SQL SERVER 2012

Upvotes: 0

ThePravinDeshmukh
ThePravinDeshmukh

Reputation: 1913

Instead of datepart use datename, It will now return you name of month instead of Month index (i.e., 12 for December)

Then get for first three letters of returned month to compare with you input string

Use this for comparing

 datepart(year, A.DateOpen)=@Year AND left(datename(month, A.DateOpen),3)=@Month

Upvotes: 0

Gimly
Gimly

Reputation: 6175

You're using DATEPART but passing it your property called @Mon which is a CHAR(3), he cannot convert this CHAR(3) to Date, hence the error.

Your @Mon parameter should be an Int like @Yr and your query be

datepart(year, A.DateOpen)=@Year AND datepart(month, A.DateOpen) = @Month

Upvotes: 1

Related Questions