How to search birthdate using sql query?

Question

I want to search Date of Birth from my sql server database table. Problem what i am getting is the year of DOB will be different and will be latter than current year. If i want to get customer list whose birthday will coming in next 7 days same as facebook birthday reminder.

I have tried to convert smalldatetime to varchar and used LIKE statement with '_' wild card character. for example Convert(Char(10),DOB,103) LIKE "__/02/____'. but, it gives me list of customers whose DOB is coming in current month. Also it shows me those data whose birthday has gone previously on current month. I want to only those data whose birthday is coming on next 7 days.

My application has developed in C#. if possible to get appropriate result using DataTable or DataView then please suggest me.

possible duplicate of [SQL Select Upcoming Birthdays](http://stackoverflow.com/questions/83531/sql-select-upcoming-birthdays) check [this Answer](http://stackoverflow.com/questions/83531/sql-select-upcoming-birthdays/83783#83783) — Damith, Feb 26 '14 at 05:26

score 2 · Accepted Answer · edited May 23 '17 at 11:59

Using this method of date assembly, this should work:

select * from Customers where DATEADD(day, DAY(DOB) -1, DATEADD(month, MONTH(DOB) - 1, DATEADD(year, YEAR(CURRENT_TIMESTAMP) - 1900, 0))) BETWEEN CURRENT_TIMESTAMP AND DATEADD(DD, 7, CURRENT_TIMESTAMP);

.

Edit
The above didn't work across the change of year. This does, although is rather clumsy (Use @Today to test scenarios, and then substitute for CURRENT_TIMESTAMP when done):

DECLARE @Today DATE = '31 Dec 2013';
select *
from Customers
CROSS JOIN
    (
        SELECT YEAR(@Today) AS yr
        UNION
        SELECT YEAR(DATEADD(d, 7, @Today))
    ) years
where 
    DATEADD(day, DAY(DOB) -1, 
           DATEADD(month, MONTH(DOB) - 1,
               DATEADD(year, years.yr - 1900, 0)))
    BETWEEN @Today AND DATEADD(DD, 7, @Today);

SqlFiddle here

The cross join will return either this year or this year AND next year if we are near to the end of year, and the UNION will ensure only one row if the year is the same. If 2 years are returned (i.e. we are late in December), then the Between will exclude the customer's DOB which happened earlier this year in January :P There must be a more elegant solution, however ...

Why not just add the difference in years between the DOB and today to the DOB and match according to that, as posted in my answer? I've used that method countless times before and it always works 100%... — FarligOpptreden, Feb 26 '14 at 05:16

FarligOpptreden · Answer 2 · 2014-02-26T07:03:06.847

1

Try fashioning a where clause to your query using the DATEPART and DATEADD and DATEDIFF functions. The following WHERE clause should get you the desired results:

SELECT *
FROM [Table]
WHERE DATEADD(yy, DATEDIFF(yy, [DOB], GETDATE()) +
          CASE
              WHEN DATEPART(mm, GETDATE()) > 
                   DATEPART(mm, DATEADD(dd, 7, GETDATE())) 
               AND DATEPART(mm, GETDATE()) <> DATEPART(mm, [DOB])
                   THEN 1 
              ELSE 0
          END, [DOB]) 
      BETWEEN GETDATE() AND DATEADD(dd, 7, GETDATE())

What that clause does, is it moves the [DOB] value to the current year using DATEADD and DATEDIFF and matches it between today and 7 days from today.

EDIT: Amended the clause to cater for cycles over year periods.

edited Feb 26 '14 at 07:03

answered Feb 26 '14 at 05:03

FarligOpptreden

5,013
22
23

This seems to also miss the edge case where today is 31 December and there is a customer's birthday coming up in the first week of January?. [SqlFiddle](http://www.sqlfiddle.com/#!6/c6675/1) (Fiddle updated) – StuartLC Feb 26 '14 at 05:36
make that http://www.sqlfiddle.com/#!6/16105/1 – StuartLC Feb 26 '14 at 05:45
Busy with an amended answer as well - just tested and you're right. – FarligOpptreden Feb 26 '14 at 06:10

score 0 · Answer 3 · answered Feb 26 '14 at 04:53

0

Use the date based functions.

//pseudo code
Where Month(birthdate) = 7
and Day(birthdate) = 4

will pull up those born on the 4th of July

answered Feb 26 '14 at 04:53

mcalex

6,628
5
50
80

This doesn't address the OP, but rather matches a date to a specific day and month. The idea is to get dates between today and 7 days from today, so you can't match it to specific days and/or months. – FarligOpptreden Feb 26 '14 at 05:09

score 0 · Answer 4 · edited Feb 26 '14 at 05:31

0

I'm not on my development machine so this is untested, but should point you in the correct direction.

SELECT *
FROM TABLE
WHERE MONTH(DOB) = MONTH(GETDATE()) AND 
(DAY(DOB) BETWEEN DAY(GETDATE()) AND 
DAY(DATEADD(dd, 7, GETDATE())))

Note that the GETDATE() function gets the current date and time for the current time zone on the machine. To use the UTC date, replace GETDATE() with GETUTCDATE().

edited Feb 26 '14 at 05:31

Matt Ball

354,903
100
647
710

answered Feb 26 '14 at 04:57

HTX9

1,717
3
15
27

1

This won't work, because as soon as it spans over two months you wont get the correct data. – FarligOpptreden Feb 26 '14 at 04:59
@FarligOpptreden Good point. I didn't consider that... – HTX9 Feb 26 '14 at 05:00

score 0 · Answer 5 · answered Feb 26 '14 at 05:52

Using StuartLC’s example as a baseline. How about building up the DOB for each record, but instead of the actual year, use the current year and next year. This way, you can take the DateDiff for the seven day range over both dates -- which handles the year rollover and is very explicit for its intent.

Example:

DECLARE @Today DATE = '31 Dec 2013';

SELECT *
FROM Customers
WHERE
      cast(
          cast(Month(DOB) AS varchar) + '/' +
          cast(Day(DOB) AS varchar) + '/' +
          cast(Year(@Today) AS varchar) AS Date) BETWEEN @Today AND dateadd(day, 7, @Today)
OR
        cast(
          cast(Month(DOB) AS varchar) + '/' +
          cast(Day(DOB) AS varchar) + '/' +
          cast(Year(@Today) + 1 AS varchar) AS Date) BETWEEN @Today AND dateadd(day, 7, @Today)

Sql Fiddle here

How to search birthdate using sql query?

5 Answers5