Between dated code using parameters and text

I'm trying to create a procedure where the user provides the month and year and I hard code the day.  But...
    1) I don't know how to do the 'end of month' day since it changes
    2) I tried to just hard code a day below but I get the error...

     Conversion failed when converting the varchar value '/1/' to data type int.
Is there a way to accomplish this?


@Month int,
@Year int
--)
AS

INSERT INTO tblSafStatsApd ( CurCnt801 )
SELECT Count(tblIncidents.InjuryId) AS CurCnt801
FROM tblIncidents
WHERE (((tblIncidents.Date) Between (@Month + '/1/' + @Year) And (@Month + '/31/' + @Year)))
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1));
BobRosasAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Or:


@Month int,
@Year int
--)
AS

DECLARE @StartDate datetime = DATEADD(month, @Month - 1, DATEADD(year, @Year - 1900, 0))
DECLARE @EndDate datetime = DATEADD(month, 1, @StartDate)

INSERT INTO tblSafStatsApd ( CurCnt801 )
SELECT Count(tblIncidents.InjuryId) AS CurCnt801
FROM tblIncidents
WHERE tblIncidents.Date >= @StartDate And tblIncidents.Date < @EndDate
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1));

Open in new window

0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Eyeballeth a couple of things:
If you're using @month and @year as integers, you have to CAST() both of them as something character, in order to concatenate it with the '/1/'.
If the month is February there is no February 31, so instead get the next month's first day, then just subtract one day to get the end of the month.

Declare @month int = 6, @year int = 2012, @dt date = GETDATE()

SELECT CAST(CAST(@Month as varchar(2)) + '-1-' + CAST(@Year  as varchar(4)) as date) as start_date,
	DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2))+ '-1-' + CAST(@Year  as varchar(4))) as date)) as end_date

Open in new window

0
 
BobRosasAuthor Commented:
Thank you so much for the code!  
Admittedly, I don't know how to incorporate your code with mine.  I tried adding the start_date and end_date to my WHERE clause but it doesn't even recognize them.  Can you please help me wih that?

(
@Month int,
@Year int
)
AS

INSERT INTO tblSafStatsApd ( CurCnt801 )
SELECT Count(tblIncidents.InjuryId) AS CurCnt801,
CAST(CAST(@Month as varchar(2)) + '-1-' + CAST(@Year  as varchar(4)) as date) as start_date,
CAST(CAST(@Month as varchar(2)) + '-' +
DATEADD(d, -1, CAST((CAST(@Month + 1  as varchar(2)))))+ '-' +
CAST(@Year  as varchar(4)) as date) as end_date
FROM tblIncidents
WHERE (((tblIncidents.Date) Between (start_date) And (end_date)))
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1));
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
BobRosasAuthor Commented:
matthewspatrick
Thank you so much for your code!  I did not see it until after I posted my last comment.  I see now I needed to Declare start and end dates.  Your code is easy to understand, it runs and gives me the expected rsults.  Can you also please explain how the day of the month works?  It looks like you subtract month (not day) from a parameter that's not even a date...very confusing to me.
0
 
Patrick MatthewsCommented:
I exploit the fact that in SQL Server, 1900-01-01 is assigned a numeric value of 0.

So, looking at my start date:

DECLARE @StartDate datetime = DATEADD(month, @Month - 1, DATEADD(year, @Year - 1900, 0))

The inner expression gets me to Jan 1 of the year I want.  I subtract 1900 because that is the year for my zero date.

The outer expression advances me a certain number of months.  I subtract 1 because I need to know how many months to advance.  For example, you would pass in 3 for March; to get from January 1 to March I need to advance 2 months.

The end date is simply one month after the start date :)

But note that with my approach, you should not use BETWEEN!

Look again at my WHERE clause.
0
 
BobRosasAuthor Commented:
Wow...thank you so much for the explanation.  I did not know any of that.  Also thank you for pointing out that you did not use "between".  Since I just copied in the code...and it worked...I wasn't focused on that.  I have maxed out points and will award to both of you since jimhorn's code would also work if I knew sql better : )
Thanks again!
0
 
BobRosasAuthor Commented:
EE is great!
0
 
Patrick MatthewsCommented:
Glad to help, and always nice to collaborate with Jim :)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<no points for this comment please>

>I don't know how to do the 'end of month' day since it changes
SQL 2012 introduces the EOM function to make this easier...

SELECT EOMONTH(@your_date)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.