• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 803
  • Last Modified:

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));
0
BobRosas
Asked:
BobRosas
  • 4
  • 3
  • 2
2 Solutions
 
Jim HornMicrosoft 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
 
Patrick MatthewsCommented:
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now