sql Year function Problem

HI,
this is simple overview of Year function in ms-sql.
http://msdn.microsoft.com/en-us/library/ms186313.aspx[^]

as i check you sql query and i think that there is one issue with conversion of datetime field to varchar.
because it is depends upon the datetime format of sql server's system sometime it when we convert datetime field to varchar it will convert month to day and day to month so might be something like issue will be happen with your sql query and db structure.

And here is another approach using DATEPART :

SELECT DateName(month,a.REQDATE) As hp ,Month(a.REQDATE) months, count(*) as counts FROM TASKS a INNER JOIN WorkOrderStatus b ON a.WorkOrderStatusId = b.WorkOrderStatusId WHERE  a.REQUEST = 'HPAD' and DATEPART(yy ,a.REQDATE)=2011 GROUP BY DateName(month,a.REQDATE) ,Month(a.REQDATE) ORDER BY Month(a.REQDATE) ASC

Use DateDiff function for this


Select ..... from <yourtablename> where Datediff(yy,<startdate>,<enddate>)


For more information go through this link

http://www.sqlteam.com/article/datediff-function-demystified[^]

http://blog.sqlauthority.com/2011/10/21/sql-server-datediff-accuracy-of-various-dateparts/[^]

See this sql between

see below text in this link

<br />
Note: The BETWEEN operator is treated differently in different databases!<br />
<br />
In some databases, persons with the LastName of "Hansen" or "Pettersen" will not be listed, <br />
<br />
because the BETWEEN operator only selects fields that are between and excluding the test <br />
<br />
values.<br />
<br />
In other databases, persons with the LastName of "Hansen" or "Pettersen" will be listed, <br />
<br />
because the BETWEEN operator selects fields that are between and including the test values.<br />

So try with this solutions

1. a.REQDATE between '01-01-2011' and '11-14-2011'
2. a.REQDATE between '31-12-2010' and '11-15-2011'