selecting only year or month from datetime field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

selecting only year or month from datetime field

Author

Topic

postmaster.chris
Starting Member

3 Posts

Posted - 2006-05-10 : 13:50:41

I have a datetime field and I'm looking for a SQL query to only return rows in the database that have the year of 2006 and the month of 5 or something like that. I know this has to be pretty straight forward, but I can't find anything.

Thank you for any help

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-05-10 : 13:58:38

Select yourfield
from yourtable
Where month(yourfield) = '5' and Year(yourfield) = '2006'

Jim
Users <> Logic

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-10 : 14:03:47

You'll normally get better performance if you don't have to apply a function directly onto a column:

SELECT yourfield
FROM yourtable
WHERE yourfield >= '2006-05-01'
AND yourfield < '2006-06-01'

digitald
Starting Member

5 Posts

Posted - 2006-05-10 : 18:20:57

quote:
Originally posted by postmaster.chris

I have a datetime field and I'm looking for a SQL query to only return rows in the database that have the year of 2006 and the month of 5 or something like that. I know this has to be pretty straight forward, but I can't find anything.

Thank you for any help

You can try:

SELECT col1
FROM tbl
WHERE datepart(yy,col2) = '2006'
AND datepart(mm,col2) = '5'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-11 : 02:57:08

nosepicker's method is the way to go to make use of index if any

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources