[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Operand type clash: date is incompatible with tinyint

Posted on 2014-08-25
19
Medium Priority
?
3,464 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
0
Comment
Question by:al4629740
19 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40284613
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

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40284697
>>"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"
0
 

Author Comment

by:al4629740
ID: 40284916
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:al4629740
ID: 40284917
Addtionally, I am simply looking for a count of those who started for the first time in each month.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40284937
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.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40284995
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

0
 

Author Comment

by:al4629740
ID: 40285751
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?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40286466
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
;
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40286899
>>"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)
0
 

Author Comment

by:al4629740
ID: 40290658
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
0
 

Author Comment

by:al4629740
ID: 40290674
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
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40291776
Try this
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

0
 

Author Comment

by:al4629740
ID: 40293709
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?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40293770
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
0
 

Author Comment

by:al4629740
ID: 40293860
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

0
 

Author Comment

by:al4629740
ID: 40293927
Found it

Agency should be AGENCYID
0
 
LVL 49

Expert Comment

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

Author Comment

by:al4629740
ID: 40293958
Thank you for your patience
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40293986
No problem, the important part is you got your solution. Cheers, Paul
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question