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 |
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....