BobRosas
asked on
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.InjuryI
FROM tblIncidents
WHERE (((tblIncidents.Date) Between (@Month + '/1/' + @Year) And (@Month + '/31/' + @Year)))
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1));
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.InjuryI
FROM tblIncidents
WHERE (((tblIncidents.Date) Between (@Month + '/1/' + @Year) And (@Month + '/31/' + @Year)))
GROUP BY tblIncidents.[801]
HAVING (((tblIncidents.[801])= 1));
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
ASKER
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!
Thanks again!
ASKER
EE is great!
Glad to help, and always nice to collaborate with Jim :)
<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)
>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)
ASKER
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.InjuryI
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));