Why does my query fail to convert a varchar value to int?

Question

My query:

SELECT
   Date_current,
   COUNT(*) AS 'Total'
FROM
   Call_Register
WHERE
   (DATEDIFF(dd,'02/1/2014',Date_current)  >=0)
   AND
   (DATEDIFF(dd,'02/12/2014',Date_current) <=0)
GROUP BY
   Date_current
HAVING
   COUNT(*)>=(convert(int,'02/12/2014'))
ORDER BY
   Date_current

Error:

Conversion failed when converting the varchar value '02/12/2014' to data type int.

Output I want:

Date	Total
Feb 3 2014 2:58PM	1
Feb 3 2014 2:59PM	1
Feb 3 2014 3:00PM	1
Feb 3 2014 3:08PM	1
Feb 3 2014 3:20PM	1
Feb 3 2014 4:05PM	1
Feb 3 2014 4:17PM	1
Feb 3 2014 4:19PM	1
Feb 3 2014 4:21PM	1
Feb 3 2014 4:24PM	1
Feb 4 2014 1:11PM	1
Feb 4 2014 2:35PM	1
Feb 4 2014 2:37PM	1
Feb 4 2014 5:19PM	1

Answer 1

Firstly, you should either use the culture invariant date format yyyyMMdd, or explicitly set the date format using SET DATEFORMAT DMY, or prepare to get inconsistent results.

Secondly, the following is potentially very inefficient:

WHERE (DATEDIFF(dd,'02/1/2014',Date_current)  >=0) 
  AND (DATEDIFF(dd,'02/12/2014',Date_current) <=0) 

If you have an index on Date_Current it will not be used because you are performing a function on it. You should instead use:

WHERE Date_Current >= '20140102'
AND Date_Current <= '20141202'

You then have a sargable query. I have had to guess at whether '02/1/2014' meant 1st February 2014, or 2nd January 2014 as it is not clear (hence the importance of my first point).

Finally (this part has already been answered but including it for completeness as I couldn't not point out the first two errors) you cannot convert to int here:

convert(int,'02/12/2014')

You presumably need to convert to date time first:

CONVERT(INT, CONVERT(DATETIME, '20141202'))

Although I suspect this is still not what you want, you are just filtering the days to those that have 41973 records or more, seems like a fairly arbitrary filter....