run a query on datestamp 1

[scottRen]

hi, i have a column called date_filed where data is of the format: 16/02/1994

i would like to run a simple query to pull all the records for year 1994

this is what i have, (also tried IN instead of contain which didn't work:

SELECT *
FROM tbl
WHERE (date_filed CONTAIN '1994')


thanks

 

[JohnDTampaBay]

What is the datatype for the field? If it's datetime or smalldatetime, then this should work:

SELECT * FROM tbl WHERE DatePart(yyyy, date_filed) = 1994

If it's varchar, then try this:

SELECT * FROM tbl WHERE Right(date_filed, 4) = '1994'



--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[scottRen]

sry i forgot, it was datetime and it worked! Thanks

 

[mrdenny]

This would have also worked.

SELECT * FROM tbl WHERE year(date_filed) = 1994

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLBill]

scottRen,

Something to keep in mind. You are using the date format of day/month/year. Sometimes SQL Server has 'trouble' interpreting dates. To resolve this, use the SET DATEFORMAT command at the beginning of your scripts.

SET DATEFORMAT DMY

lets SQL Server know that date inputs are in "day first, month next, year last" format.

-SQLBill

Posting advice: FAQ481-4875

 

[JamesLean]

One point I would also make is that if there is an index on this field, using a function like DATEPART or YEAR will stop it being used. Using a search like below will utilise the index if appropriate:

SELECT * FROM t
WHERE date_filed >= '20040101'
  AND date_filed < '20050101'

--James