DateTime query on only year in SQL Server
- You need to delimit datetime constants like you would a string
- Use
yyyymmdd
pattern for SQL Server (notyyyy-mm-dd
, it is unsafe generally) - Use proper range queries to allow for time value
So, to find values between "12 Dec 2004" and "31 Dec 2009" inclusive, allowing for time:
...
where [IssueDate] >= '20041212' AND [IssueDate] < '20100101'
Edit, or this, because of the ambiguity in the question for "year = 2009"
...
where [IssueDate] >= '20090101' AND [IssueDate] < '20100101'
select id,name,bookyear from tab1 where year(bookyear) = 2009
SELECT [ACCNO]
,[Roll No]
,[IssueDate]
,[DueDate]
FROM [test1].[dbo].[IssueHis$]
WHERE [IssueDate] >= '20090101' AND
[IssueDate] < '20100101'