Try to
convert[
^]:
SELECT *
FROM table_a
WHERE CONVERT(DATETIME, [date],112) BETWEEN @startDate AND @endDate
And please, read this:
sum of row value date wise[
^] to know what bad table design can cause.
[EDIT]
amritha444 - 17 mins ago wrote:
here is some of column values
24/5/2013
24/5/2013
2/7/2013
2/7/2013
25/6/2013
25/6/2013
6/6/2013
27/5/2013
OK, try add SET DATEFORMAT instruction:
SET DATEFORMAT dmy;
DECLARE @tmp TABLE (dateval VARCHAR(30))
INSERT INTO @tmp (dateval)
SELECT '24/5/2013'
UNION ALL SELECT '24/5/2013'
UNION ALL SELECT '2/7/2013'
UNION ALL SELECT '2/7/2013'
UNION ALL SELECT '25/6/2013'
UNION ALL SELECT '25/6/2013'
UNION ALL SELECT '6/6/2013'
UNION ALL SELECT '27/5/2013'
UNION ALL SELECT '27/5/2013'
UNION ALL SELECT NULL
UNION ALL SELECT '7/13/2113'
UNION ALL SELECT '12/12/2215'
SELECT dateval, CONVERT(INT, LEFT(dateval, CHARINDEX('/',dateval)-1)) AS [d],
CONVERT(INT, SUBSTRING(dateval, CHARINDEX('/',dateval)+1,CHARINDEX('/',dateval, CHARINDEX('/',dateval)+1) - CHARINDEX('/',dateval)-1)) AS [m],
CONVERT(INT, RIGHT(dateval,4)) AS [y]
FROM @tmp
WHERE CHARINDEX('/',dateval)>0
Result:
24/5/2013 24 5 2013
24/5/2013 24 5 2013
2/7/2013 2 7 2013
2/7/2013 2 7 2013
25/6/2013 25 6 2013
25/6/2013 25 6 2013
6/6/2013 6 6 2013
27/5/2013 27 5 2013
27/5/2013 27 5 2013
7/13/2113 7 13 2113
12/12/2215 12 12 2215
[/EDIT]