We help IT Professionals succeed at work.

Operand type clash: date is incompatible with tinyint

al4629740
al4629740 asked
on
6,656 Views
Last Modified: 2014-08-29
I get the following error.  Any ideas what it could be?

Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with tinyint

Code below:
select * from tblOrgHours where Agencyid = 74 and Fiscal = 2015 And ActivityDate >= '07/01/2014' And ActivityDate <= '06/30/2015' having min(activitydate) = 7

Open in new window


Basically, within this data range, I want all records that begin in the month specified.  In this case, its July
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2013

Commented:
You have a few issues. First of all, you are incorrectly using your HAVING clause. This clause is used after the GROUP BY clause to filter the aggregate function. Your query does not have the GROUP BY clause, so you will not be able to use the HAVING clause.
Second, to filter by month you need to extract month from the date. In SQL Server you need to use DATEPART function.
As I understand you correctly, you want to retrieve the records for the whole year, but at the same time you want to see which one of those originated in July. For this exercise the best approach is to use a WINDOW Function.
select *,
min(activitydate) OVER(PARTITION BY Fiscal) minActivity
from tblOrgHours 
where Agencyid = 74 and Fiscal = 2015 And 
      ActivityDate >= '07/01/2014' And ActivityDate <= '06/30/2015' 

Open in new window

The minActivity column will contain the date you are after. However, you will not be able to use it in the WHERE clause straight away. You will have to use a sub-query:
SELECT * FROM (
select *,
min(activitydate) OVER(PARTITION BY Fiscal) minActivity
from tblOrgHours 
where Agencyid = 74 and Fiscal = 2015 And 
      ActivityDate >= '07/01/2014' And ActivityDate <= '06/30/2015') as s
WHERE DATEPART(month, minActivity) = 7

Open in new window

Alternatively, you can use a CTE:
;with s as (
select *,
min(activitydate) OVER(PARTITION BY Fiscal) minActivity
from tblOrgHours 
where Agencyid = 74 and Fiscal = 2015 And 
      ActivityDate >= '07/01/2014' And ActivityDate <= '06/30/2015')
SELECT * FROM s
WHERE DATEPART(month, minActivity) = 7

Open in new window

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"I want all records that begin in the month specified.  In this case, its July"
Don't try to extract the month from dates. This requires running a function on hundreds, or thousands, or hundreds of thousands, or millions of rows, just so you can use 7 in your query.

Instead, alter your way of thinking to suit the way the data is stored. All you need is a simple date range. This way there is no need to run a function on each row (this is called using "sargable predicates").
SELECT
      *
FROM tblOrgHours
WHERE Agencyid = 74
      AND Fiscal = 2015
      AND ActivityDate >= '20140701' -- equal or greater than 1st July 2014
      AND ActivityDate < '20140801' -- less than 1st August 2014, do NOT use equal here

Open in new window

Please use date ranges the right way:
the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan

for more on this topic please see: "Beware of Between"

Author

Commented:
I am not sure I was clear enough on my initial question.  My fault.

Over the fiscal year of 20140701 to 20120630, I am looking for start dates of each person in the table tblOrgHours
If a 15 people started their hours for the year in July then I would like to know how many.  If I change the number to 8-August, then I would like to know how many started for the first time in August during the specified fiscal range that I indicated.

Author

Commented:
Addtionally, I am simply looking for a count of those who started for the first time in each month.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
That is quite a difference!

I think this is what you are seeking - but I cannot test with the tables and data.
declare @st as date, @fin as date

set @st = '20140701'

set @fin = dateadd(month,1,@st) -- with @st 1st July 2014, then @fin is 1st August 2014

SELECT
      COUNT(CASE
            WHEN ActivityDate >= @st 
             AND ActivityDate  < @fin -- less than 1st August 2014, do NOT use equal here
            THEN ActivityDate 
             END) AS count_of
FROM tblOrgHours
WHERE Agencyid = 74
      AND ActivityDate  < @fin -- less than 1st August 2014, do NOT use equal here
HAVING
      COUNT(CASE
            WHEN ActivityDate < @st THEN ActivityDate END) = 0
;

Open in new window

The best way for this type of need to for you to provide "sample data" and an "expected result", then we do have a way to test.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
actually I think that probably isn't going to work, sigh. Try this instead
DECLARE @st AS date
      , @fin AS date

SET @st = '20140701'

SET @fin = DATEADD(MONTH, 1, @st) -- with @st 1st July 2014, then @fin is 1st August 2014

SELECT
      COUNT(*) AS count_of
FROM (
            SELECT
                  employee_id
            FROM tblOrgHours
            WHERE Agencyid = 74
                  AND ActivityDate < @fin -- less than 1st August 2014, do NOT use equal here
            GROUP BY
                  employee_id
            HAVING COUNT(CASE
                  WHEN ActivityDate < @st THEN ActivityDate END) = 0
      ) sq;

Open in new window

Author

Commented:
Paul, that is getting very close.  However I return 0 results from this query.  There is definitely data that should be returned.  Is it something in the HAVING clause?
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Is the min(ActivityDate) what determines when the employee started? If so, something like the following might work -
select startdate, employee_id, datepart('mm',startdate) startmonth,
count(empidloyee_id) over (partition by datepart('mm',startdate) order by startdate) as monthscount
from
(select employee_id, min(activitydate) startdate
 from tblOrgHours
 where activitydate >= to_date('20140701','yyyymmdd') and activitydate < to_date('20150701','yyyymmdd')
 group by employee_id
 order by employee_id) as x
;
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"There is definitely data that should be returned. "
it's time for you to prove that statement by providing some data, and the expected result

"sample data" does not have to be huge and it doesn't need anything private
"expected result" should be based on the sample data

This is a time proven method for getting you the results you want, quickly
(ps: had this been done in the question you would probably have awarded points already)

Author

Commented:
Here is the table.  I hope this comes appears clean enough

HourID	Agency	AgencyID	Program	ActivityID	RegID	ActivityDate	Hours	HourTimeFrom	HourTimeTo	SubGroup	ParticipantHour	VolunteerHour	Fiscal	EntryTime
180	Administrator	74	CSW	3	35	7/3/2014	1	00:00.0	00:00.0		1	0	2015	7/3/2014
181	Administrator	74	CSW	3	9	7/3/2014	1	00:00.0	00:00.0		1	0	2015	7/3/2014
183	Administrator	74	CSW	3	6	7/3/2014	1	00:00.0	00:00.0		1	0	2015	7/3/2014
185	Administrator	74	CSW	3	3	7/3/2014	1	00:00.0	00:00.0		1	0	2015	7/3/2014

Open in new window


Here is what I would like to achieve:

NumberofthoseStarted
12

Author

Commented:
The Number of those Started would be in reference to how many began their very first month of activity hours

Here is a better view of tblOrgHours
tblOrgHours.xlsx
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I got this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Administrator' to data type int.


Why would that be happening?
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
gosh, with the query used for that error message being on your screen only I would have to say I don't know exactly.

somewhere in that query a string is being converted to integer, which won't work.

hint: although you may think it is redundant, you need to provide the query that you are using so we can look for the problem

Author

Commented:
SELECT
      COUNT(DISTINCT RegID)
FROM (
            SELECT
                  RegID
                , MIN(ActivityDate) OVER (PARTITION BY RegID) AS RegStartMonth
            FROM tblOrgHours
            WHERE fiscal = 2015 AND Agency = 74
      ) sq
WHERE MONTH(RegStartMonth) = 7
;

Open in new window

Author

Commented:
Found it

Agency should be AGENCYID
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
<grin> and I probably wouldn't have got that anyway (that detail is a long way up from here) :: well done

Author

Commented:
Thank you for your patience
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
No problem, the important part is you got your solution. Cheers, Paul
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.