Solved

# Between dated code using parameters and text

Posted on 2012-09-05
733 Views
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
Question by:BobRosas

LVL 65

Assisted Solution

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
``````
0

LVL 92

Accepted Solution

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));
``````
0

Author Comment

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

Author Comment

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

LVL 92

Expert Comment

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

Author Comment

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

Author Closing Comment

EE is great!
0

LVL 92

Expert Comment

Glad to help, and always nice to collaborate with Jim :)
0

LVL 65

Expert Comment

<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

### Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.