How can I check whether a month/day falls within a date range?

[Anonymous]

Hello all,

I have some data that looks like this:

StudentID	DOB	StartDate	EndDate	Birthday?
31421	08/11/1960	22/10/2017	28/10/2017
46287	16/08/2002	22/10/2017	28/10/2017
51771	28/09/2000	22/10/2017	28/10/2017
51776	03/11/2004	22/10/2017	04/11/2017	Yes
51858	04/01/1961	11/10/2017	18/10/2017

I'm trying to determine whether a student will have a birthday during their course so I need to check whether the month/day of the DOB will fall between the StartDate and EndDate (inclusive). What's the best way of doing this?

[sunny_talwar]

May be like this

LOAD *,

If(SetDateYear(DOB, Year(Today())) >= SetDateYear(StartDate, Year(Today())) and SetDateYear(DOB, Year(Today())) <= SetDateYear(EndDate, Year(Today())), 'Yes') as Birthday?;

LOAD * INLINE [

    StudentID, DOB, StartDate, EndDate

    31421, 08/11/1960, 22/10/2017, 28/10/2017

    46287, 16/08/2002, 22/10/2017, 28/10/2017

    51771, 28/09/2000, 22/10/2017, 28/10/2017

    51776, 03/11/2004, 22/10/2017, 04/11/2017

    51858, 04/01/1961, 11/10/2017, 18/10/2017

];

[antoniotiman]

If(DayNumberOfYear(DOB) >= DayNumberOfYear(StartDate) and DayNumberOfYear(DOB) <= DayNumberOfYear(EndDate),'Yes','No')

[Anonymous]

Brilliant - thank you both!

[swuehl · Accepted solution]

Are you sure this returns the correct result even when the course crosses year change?

I would like to suggest

If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as Birthday?

SET DATEFORMAT = 'DD/MM/YYYY';

LOAD *,

If(DayNumberOfYear(DOB) >= DayNumberOfYear(StartDate) and DayNumberOfYear(DOB) <= DayNumberOfYear(EndDate),'Yes','No') as B1?,

If(SetDateYear(DOB, Year(Today())) >= SetDateYear(StartDate, Year(Today())) and SetDateYear(DOB, Year(Today())) <= SetDateYear(EndDate, Year(Today())), 'Yes') as Birthday?,

If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as B2?

;

LOAD * INLINE [

    StudentID, DOB, StartDate, EndDate

    31421, 08/11/1960, 22/10/2017, 28/10/2017

    46287, 16/08/2002, 22/10/2017, 28/10/2017

    51771, 28/09/2000, 22/10/2017, 28/10/2017

    51776, 03/11/2004, 22/10/2017, 04/11/2017

    51858, 04/01/1961, 11/10/2017, 18/10/2017

    52222, 01/01/1970, 31/12/2017, 02/01/2018

    52223, 31/12/1970, 31/12/2017, 02/01/2018

    52224, 02/01/1970, 31/12/2017, 02/01/2018   

    52225, 30/12/1970, 31/12/2017, 02/01/2018

    52226, 03/01/1970, 31/12/2017, 02/01/2018   

];

[Anonymous]

This is even better! Thank you 🙂