filter by year

I'm using a datetime column (createddate), and passing a smallint variable
(@.year). what's the best way to filter records that fall in that specific
year (including 01/01 and 12/31 records).
Thanks!Guy
CREATE TABLE #Test
(
col DATETIME NOT NULL PRIMARY KEY
)
INSERT INTO #Test VALUES ('20000101')
INSERT INTO #Test VALUES ('20010101')
INSERT INTO #Test VALUES ('20020101')
INSERT INTO #Test VALUES ('20020102')
INSERT INTO #Test VALUES ('20020103')
INSERT INTO #Test VALUES ('20030101')
DECLARE @.y AS SMALLINT
SET @.y=2002
SELECT * FROM #Test WHERE YEAR(col)=@.y
If it does not help you please post DDL+ sample data + expected result.
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:%23kqBn6bEFHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm using a datetime column (createddate), and passing a smallint variable
> (@.year). what's the best way to filter records that fall in that specific
> year (including 01/01 and 12/31 records).
> Thanks!
>|||Here's one method:
SELECT *
FROM YourTable
WHERE createddate >= DATEADD(YEAR,@.year-2000,'20000101')
AND createddate < DATEADD(YEAR,@.year-1999,'20000101')
David Portas
SQL Server MVP
--|||In addition to the other replies, you might want to implement a calendar
table if you do these kinds of things on a regular basis.
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:%23kqBn6bEFHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm using a datetime column (createddate), and passing a smallint variable
> (@.year). what's the best way to filter records that fall in that specific
> year (including 01/01 and 12/31 records).
> Thanks!
>