0

I am trying to figure out if a certain event happened within a few weeks of someone's birthday. The year doesn't matter; only the month and day.

For example, I want to know if the event (January 5) is within 4 weeks of the birthday (Dec 18).

In SQL, I have SELECT ..... where abs(week(event)-week(birthday))<5

This works well if both the event and the birthday are between weeks 4 and 48 in the year.

How can I set up the query so that it works for the edge months?

mankowitz
  • 1,864
  • 1
  • 14
  • 32
  • what do you mean by edge months? – Ramesh Babu Nov 04 '15 at 11:07
  • what I meant was that in january, the week is going to be 1-4, while in december, the week will be 48 or more. So the problem is that if you compare Dec. 18 and January 5, they will be in weeks 50 and 2, respectively. If you do `abs(50-2)` you get 48, which makes it seem that the two dates are more than 3 weeks apart. – mankowitz Nov 05 '15 at 02:28

2 Answers2

1

In SQL Server I'd use DATEDIFF and % (modulo).

SELECT
...
WHERE
(ABS(DATEDIFF(day, @date1, @date2)) % 365) < 4*7
OR
(ABS(DATEDIFF(day, @date1, @date2)) % 365) > 365 - 4*7

To make calculations correct even when @date1 and @date2 are in different years with leap years in between, I'd move both dates into the same year. It depends on the DBMS you use, how to change the year component of the date, but once both dates are in the same year modulo is not needed:

SELECT
...
WHERE
ABS(DATEDIFF(day, @date1, @date2)) < 4*7
OR
ABS(DATEDIFF(day, @date1, @date2)) > 365 - 4*7
Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
0

You can use the function DATEDIFF:

SELECT DATEDIFF(week,'2014-06-05','2015-08-05') AS DiffDateInWeeks

Note that this function might have a different name (or parameters) depending on the DBMS. For MySQL for example the DATEDIFF function has no 'date part' parameter, so you could do something like in this answer:

SELECT DATEDIFF('2014-06-05','2015-08-05')/7.0 AS DiffDateInWeeks
Community
  • 1
  • 1
agold
  • 6,140
  • 9
  • 38
  • 54