How to get rows when only querying on the month of a birth date

Hi All

This is a good one that's baked my noodle!!

I have a standard table with lets say 2 cols:

NAME string
DOB date

Example rows may be:

NAME DOB
Gary Barlow 24-3-2002
John Barlow 24-11-2001
Bob Barlow 7-7-1999
Bill Barlow 6-3-1999
etc

I need to build a query that will get me all the rows back that were born in
say March (3), which means the above would bring back Gary and Bill even
though the birth day and year might be different - is this possible??!?!?

Thanks

Re: How to get rows when only querying on the month of a birth date

> I need to build a query that will get me all the rows back that were born
> in
> say March (3), which means the above would bring back Gary and Bill even
> though the birth day and year might be different - is this possible??!?!?

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.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blackberry" wrote in message
news:urolX6GJIHA.5400@TK2MSFTNGP04.phx.gbl...
> Hi All
>
> This is a good one that's baked my noodle!!
>
> I have a standard table with lets say 2 cols:
>
> NAME string
> DOB date
>
> Example rows may be:
>
> NAME DOB
> Gary Barlow 24-3-2002
> John Barlow 24-11-2001
> Bob Barlow 7-7-1999
> Bill Barlow 6-3-1999
> etc
>
> I need to build a query that will get me all the rows back that were born
> in
> say March (3), which means the above would bring back Gary and Bill even
> though the birth day and year might be different - is this possible??!?!?
>
> Thanks
>
>
>

Re: How to get rows when only querying on the month of a birth date

Blackberry wrote:
> Hi All
>
> This is a good one that's baked my noodle!!
>
> I have a standard table with lets say 2 cols:

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.
>
> NAME string
> DOB date

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

>
> Example rows may be:
>
> NAME DOB
> Gary Barlow 24-3-2002
> John Barlow 24-11-2001
> Bob Barlow 7-7-1999
> Bill Barlow 6-3-1999
> etc
>
> I need to build a query that will get me all the rows back that were
> born in say March (3), which means the above would bring back Gary
> and Bill even though the birth day and year might be different - is
> this possible??!?!?
>

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.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: How to get rows when only querying on the month of a birth date

Hi


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

"Blackberry" wrote in message
news:urolX6GJIHA.5400@TK2MSFTNGP04.phx.gbl...
> Hi All
>
> This is a good one that's baked my noodle!!
>
> I have a standard table with lets say 2 cols:
>
> NAME string
> DOB date
>
> Example rows may be:
>
> NAME DOB
> Gary Barlow 24-3-2002
> John Barlow 24-11-2001
> Bob Barlow 7-7-1999
> Bill Barlow 6-3-1999
> etc
>
> I need to build a query that will get me all the rows back that were born
> in
> say March (3), which means the above would bring back Gary and Bill even
> though the birth day and year might be different - is this possible??!?!?
>
> Thanks
>
>
>