-2

I have rundown of records from which I need to extricate Birthdays between two given dates, paying little heed to the year.

That is, I need the birthdays falls between dates, say 2015-12-01 and 2015-12-31

The straightforward between Query checks whether the Date of Birth fields falls between these two or not.

It would be ideal if you offer assistance...

Much obliged

Manish Kumar
  • 509
  • 5
  • 15

2 Answers2

1

You need to extract the year from the 2 dates supplied. Get all bnithrdays into a temp table. Substitute the year of the the birthdays in the temp table for the one you extracted in step 1. Then run a BETWEEN query

SELECT * FROM TmpDates WHERE TempDates.Birthdate BETWEEN @DATE2 AND @DATE2

Careful - don't send a Happy birhday to anyone who has died!

AntDC
  • 1,807
  • 14
  • 23
  • OP said the YEAR is ignored (little heed paid). Your query doesn't take this into consideration. Also... I think you would find this valuable: https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common – S3S Jul 26 '16 at 13:13
  • 1
    if the date supplied is 28 Feb 2015 and you have someone born on 29 Feb 2000, then if you substitute 2015 for 2000 then BOOM! error on leap year – Cato Jul 26 '16 at 13:32
  • scsimon - If you read it all you see I am ignoring the year as I substitute in the temp table. – AntDC Jul 26 '16 at 15:49
-1

Here is a way using MONTH and DATEPART

if object_id('tempdb..#temp') is not null drop table #temp

declare @dt1 datetime, @dt2 datetime
set @dt1 = '2015-12-01' 
set @dt2 = '2015-12-31'

select '11/1/2015' as DT
into #temp
union all
select '12/15/2016'
union all
select '12/16/2017'
union all
select '1/1/2017'

select * from #temp
where 
    MONTH(DT) >= MONTH(@dt1) and MONTH(DT) <= MONTH(@dt2)
    and DATEPART(day,DT) >= DATEPART(day,@dt1) and DATEPART(day,DT) <= DATEPART(day,@dt2)
S3S
  • 24,809
  • 5
  • 26
  • 45
  • that looks wrong if the months in the date range are far apart, since if it was 10 march until 25 August, then 02 july gets excluded in your logic – Cato Jul 26 '16 at 13:26
  • @AndrewDeighton i fixed it but it's as pointless to post as are the comments since it's a duplicate question – S3S Jul 26 '16 at 13:46
  • it might be a duplicate question, but the answers at the other question don't look very good to me, I mean I'm sure you have fixed yours and it won't be very complicated - but at the accepted answer there are reams of SQL with temp tables, date artihmetic, possibly ignoring leap years etc – Cato Jul 26 '16 at 14:01
  • @AndrewDeighton not sure what that has to do with this post but ok. It'd be like me commenting about you using BETWEEN one of your posts regarding DATETIME on this thread... suggesting you read this https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common ... doesn't make a lot of sense to me but cheers! – S3S Jul 26 '16 at 14:06
  • how can the answers 'improve' if a repeat question gets locked? - that's my point - I'm sure your method is an improvement on some of stuff at the duplicate question - no the blog about Between doesn't make much sense to me - Between can be useful if used properly, well it can certainly work – Cato Jul 26 '16 at 14:15
  • @AndrewDeighton fair enough. Though most places that matter, if you are born on Feb 29 your legal DOB isn't the 29th... it's 3/1 or 2/28. And I'd look at the blog a little closer, but that's just me. Cheers. – S3S Jul 26 '16 at 18:57