BWFC (9/26/2014)
What exactly is the problem? You need to be very careful using BETWEEN with datetime. You're much better using >= and <=.
Looks ok to me:
-- Non-SARGable
;WITH MyTable AS (
SELECT CreatedOn = CAST('20140731' AS DATETIME) UNION ALL
SELECT CAST('20140801' AS DATETIME) UNION ALL
SELECT CAST('20140802' AS DATETIME) UNION ALL
SELECT CAST('2014-09-24 14:49:35.190 ' AS DATETIME) UNION ALL
SELECT CAST('20140925' AS DATETIME) UNION ALL
SELECT CAST('20140926' AS DATETIME) UNION ALL
SELECT CAST('20140927' AS DATETIME)
)
SELECT *
FROM MyTable
WHERE CONVERT(VARCHAR(10),CreatedOn,103) BETWEEN '01/08/2014' AND '26/09/2014'
-- SARGable
;WITH MyTable AS (
SELECT CreatedOn = CAST('20140731' AS DATETIME) UNION ALL
SELECT CAST('20140801' AS DATETIME) UNION ALL
SELECT CAST('20140802' AS DATETIME) UNION ALL
SELECT CAST('2014-09-24 14:49:35.190 ' AS DATETIME) UNION ALL
SELECT CAST('20140925' AS DATETIME) UNION ALL
SELECT CAST('20140926' AS DATETIME) UNION ALL
SELECT CAST('20140927' AS DATETIME)
)
SELECT *
FROM MyTable
WHERE CAST(CreatedOn AS DATE) BETWEEN CAST('20140801' AS DATE) AND CAST('20140926' AS DATE)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden