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
 General SQL Server Forums
 New to SQL Server Programming
 Between Months Spanning a Year

Author  Topic 

rypi
Yak Posting Veteran

55 Posts
Posted - 2011-12-14 : 14:50:26
I have a query that gets all employees whoes birthday falls between 2 selected dates.


SELECT
    E.ID,
    E.FirstName + ' ' + E.LastName AS NAME,
    E.DateOfBirth
FROM
    Employees E
WHERE
    MONTH(E.DateOfBirth) BETWEEN MONTH('11-24-2011') AND MONTH('01-07-2012')


Works fine when the dates don't span over the year.
But when I want to see everyone who has a birthday between december and january for example, nothing is returned. I see why it's not getting any data, but don't know how to fix it.

Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-12-14 : 15:05:15
You're just looking at the Month part
SELECT
E.ID,
E.FirstName + ' ' + E.LastName AS NAME,
E.DateOfBirth
FROM
Employees E
WHERE
E.DateOfBirth BETWEEN '11-24-2011' AND '01-07-2012'

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

rypi
Yak Posting Veteran

55 Posts
Posted - 2011-12-14 : 15:16:57
Thanks for the quick reply Jim.

However, that won't work either.

If an employee's birthday is '12-14-1975' that won't show up in the results.

I only want to look at the month part. I want to see everbody who has a birthday in the months of December and January, regardless of the year they were born.

Cheers.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2011-12-14 : 15:54:38
quote:
Originally posted by rypi

Thanks for the quick reply Jim.

However, that won't work either.

If an employee's birthday is '12-14-1975' that won't show up in the results.

I only want to look at the month part. I want to see everbody who has a birthday in the months of December and January, regardless of the year they were born.

Cheers.


You probably just need 

WHERE MONTH(E.DateOfBirth) IN (1,12)
Go to Top of Page

rypi
Yak Posting Veteran

55 Posts
Posted - 2011-12-14 : 16:10:25
quote:
Originally posted by vijayisonly

quote:
Originally posted by rypi

Thanks for the quick reply Jim.

However, that won't work either.

If an employee's birthday is '12-14-1975' that won't show up in the results.

I only want to look at the month part. I want to see everbody who has a birthday in the months of December and January, regardless of the year they were born.

Cheers.


You probably just need 

WHERE MONTH(E.DateOfBirth) IN (1,12)




I don't think that will work either. What if the date range is something like 08-15-2011 to 02-15-2012.

Cheers.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2011-12-14 : 16:22:03
Well you said
quote:
I only want to look at the month part. I want to see everbody who has a birthday in the months of December and January, regardless of the year they were born.

and gave an example of a 1975 date.

I'm not too sure what exactly your requirement is.
How about combine both Jim's and my solution and try
WHERE
E.DateOfBirth BETWEEN '11-24-2011' AND '01-07-2012' 
AND MONTH(E.DateOfBirth) IN (1,12)
Go to Top of Page

rypi
Yak Posting Veteran

55 Posts
Posted - 2011-12-14 : 16:52:32
Sorry for not being more specific.

The requirement is to select all employees in the table whose birthday month falls between a selected start and end date.

A couple of examples would be to see all employees whose birthday month falls between '01-01-2011' to '08-08-2011' or '09-10-2011' to '01-01-2012'.

Does this make more sense?

Cheers.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2011-12-14 : 22:19:12
quote:
Originally posted by rypi

I have a query that gets all employees whoes birthday falls between 2 selected dates.


SELECT
    E.ID,
    E.FirstName + ' ' + E.LastName AS NAME,
    E.DateOfBirth
FROM
    Employees E
WHERE
    MONTH(E.DateOfBirth) BETWEEN MONTH('11-24-2011') AND MONTH('01-07-2012')


Works fine when the dates don't span over the year.
But when I want to see everyone who has a birthday between december and january for example, nothing is returned. I see why it's not getting any data, but don't know how to fix it.

Thanks!


What is the data type of the DateOfBirth column? DATETIME, DATE, or something else?


This code will never return anything, because it is looking for a month that is >= 11 and <= 1.
WHERE
    MONTH(E.DateOfBirth) BETWEEN MONTH('11-24-2011') AND MONTH('01-07-2012')


If you want to do an search for any date range, you will need to find their birthday for the start year and end year and see if either of those days are between the start date and end date. Obviously, any date range that covers an entire year or more will contain their birthday.

select
    *,
    [Birthday Start year] = dateadd(yy,datediff(yy,DateOfBirth,'11-24-2011'),DateOfBirth),
    [Birthday End year]   = dateadd(yy,datediff(yy,DateOfBirth,'01-07-2012'),DateOfBirth)
from
    ( -- Test Data
    select DateOfBirth = convert(datetime,'19541213')  union all
    select DateOfBirth = convert(datetime,'19600229')
    ) a
Results:
DateOfBirth             Birthday Start year     Birthday End year
----------------------- ----------------------- -----------------------
1954-12-13 00:00:00.000 2011-12-13 00:00:00.000 2012-12-13 00:00:00.000
1960-02-29 00:00:00.000 2011-02-28 00:00:00.000 2012-02-29 00:00:00.000


That should be enough for you to come up with your query condition.





CODO ERGO SUM
Go to Top of Page

rypi
Yak Posting Veteran

55 Posts
Posted - 2011-12-15 : 02:35:23
This works:


SELECT
    E.ID,
    E.FirstName + ' ' + E.LastName AS NAME,
    E.DateOfBirth
FROM
    Employee E
WHERE
Cast(DATEDIFF(dd, E.DateOfBirth, @pStartDate) / 365 as int)
- Cast(DATEDIFF(dd, E.DateOfBirth, @pEndDate) / 365 as int) <> 0

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2011-12-15 : 10:15:54
quote:
Originally posted by rypi

This works:


SELECT
    E.ID,
    E.FirstName + ' ' + E.LastName AS NAME,
    E.DateOfBirth
FROM
    Employee E
WHERE
Cast(DATEDIFF(dd, E.DateOfBirth, @pStartDate) / 365 as int)
- Cast(DATEDIFF(dd, E.DateOfBirth, @pEndDate) / 365 as int) <> 0




Years are not always 365 days.

Here are just four examples where that code does not work.

select
	a.*, b.*,
	[Birthday Start year] = dateadd(yy,datediff(yy,DateOfBirth,'11-24-2011'),DateOfBirth),
	[Birthday End year]   = dateadd(yy,datediff(yy,DateOfBirth,'01-07-2012'),DateOfBirth),
	BirthdayInRange =
	Cast(DATEDIFF(dd, a.DateOfBirth, b.StartDate) / 365 as int)-
	Cast(DATEDIFF(dd, a.DateOfBirth, b.EndDate  ) / 365 as int)
from
	( -- Test Data
	select DateOfBirth = convert(date,'19600229') union all
	select DateOfBirth = convert(date,'19640301')
	) a
	cross join
	(-- Test Start/End Date Range
	select StartDate = convert(date,'20110228'), EndDate = convert(date,'20110501') union all
	select StartDate = convert(date,'20120101'), EndDate = convert(date,'20120229')
	) b
order by
	a.DateOfBirth,
	b.StartDate,
	b.EndDate

Results:

DateOfBirth StartDate  EndDate    Birthday Start year Birthday End year BirthdayInRange 
----------- ---------- ---------- ------------------- ----------------- --------------- 
1960-02-29  2011-02-28 2011-05-01 2011-02-28          2012-02-29                      0 
1960-02-29  2012-01-01 2012-02-29 2011-02-28          2012-02-29                     -1 
1964-03-01  2011-02-28 2011-05-01 2011-03-01          2012-03-01                      0 
1964-03-01  2012-01-01 2012-02-29 2011-03-01          2012-03-01                     -1 

(4 row(s) affected)


CODO ERGO SUM
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site