Webmaster Forum / ASP / Database Access / November 2007

Assuming DOB is a smalldatetime or datetime, you can use MONTH (or
DATEPART).  For example:

SELECT NAME, DOB
FROM dbo.MyTable
WHERE
   MONTH(DOB) = 3

Note that this method will require a scan of the table or index.  If you
need to do this sort of query against a large table and performance is
important, you might consider creating a separate column  (perhaps computed)
with an index.

What database? Type and version please.

Oh wait. you crossposted this to sqlserver.programming, so I guess that
means you're using SQL Server. The version you are using is helpful
information.

I assume you mean "datetime", not "date" ... ?

The first thing you need to realize is that dates are not stored in that
d-m-yyyy format, unless you are storing strings instead of actual datetimes.
Datetimes are stored as paired integers, with the first integer representing
the date as the number of days since the seed date, and the second
representing the time of day as the number ofmilliseconds since midnight.

The simplistic answer is to use DATEPART to extract the month from the
dates:

WHERE DATEPART(m,DOB) = 3

The only problem with this solution is that it will force a scan even if an
index exists on DOB. If you are using SQL 2005, you micht consider creating
an indexed calculated column called MonthOfBirth using the DATEPART formula
to extract the month from DOB. Now the query is both simple and efficient.

Hi

SELECT * FROM tbl WHERE dt>'20070301' and dt < '20070401'