sql显示开始日期和结束日期之间的日期

I would like to know how I can compare a birthday date between a start date and end date.

我想知道如何比较开始日期和结束日期之间的生日日期。

For now I use this:

现在我用这个:

select *
from users
where dayofyear(birthday) between dayofyear(start_date) and dayofyear(end_date);

the problem is if start_date = 2011-12-01 and end_date = 2012-01-01 then I get no results.

问题是如果start_date = 2011-12-01和end_date = 2012-01-01那么我没有得到任何结果。

If the user birthday is 1931-12-12 any year, it must be in the query result.

如果用户生日是1931-12-12任何一年,它必须在查询结果中。

For now i get no result.

现在我没有结果。

someone have a hint to resolve this kind of issue? Thanks.

有人提示要解决这类问题吗?谢谢。

1 个解决方案

#1

The following is a little ugly but it accounts for all birthdays that occur in the time span between start_date and end_date. If the time span is greater than a year then it basically returns everyone because everyone has a birthday somewhere in that time span. If it is less than a year but start and end are on different years it uses two between clauses to capture both the start time to the end of the year and the start of the year to the end time. This query also assumes that start_date <= end_date.

以下内容有点难看,但它说明了在start_date和end_date之间的时间跨度内发生的所有生日。如果时间跨度大于一年,那么它基本上会返回所有人,因为每个人都在那个时间跨度的某个地方生日。如果它不到一年,但是开始和结束是在不同的年份,它使用两个条款来捕获到年末的开始时间和一年的开始到结束时间。此查询还假定start_date <= end_date。

As long as you have an index on DAYOFYEAR( birthday ) this query is performant despite it's appearance.

只要你有DAYOFYEAR(生日)的索引,这个查询就会表现出来,尽管它的外观很好。

SET @start_date = '2011-12-01';
SET @end_date = '2012-01-01';

SELECT * FROM users
WHERE DAYOFYEAR( birthday )
  BETWEEN IF( YEAR( @end_date ) - YEAR( @start_date ) > 1, 1,
    IF( YEAR( @end_date ) - YEAR( @start_date ) > 0,
      IF( DAYOFYEAR( @start_date ) <= DAYOFYEAR( @end_date ), 1,
        DAYOFYEAR( @start_date ) ),
      DAYOFYEAR( @start_date ) ) )
      AND IF( YEAR( @end_date ) - YEAR( @start_date ) > 1, 366,
            IF( YEAR( @end_date ) - YEAR( @start_date ) > 0, 366,
              DAYOFYEAR( @end_date ) ) )
  OR DAYOFYEAR( birthday )
  BETWEEN IF( YEAR( @end_date ) - YEAR( @start_date ) > 1, 1,
    IF( YEAR( @end_date ) - YEAR( @start_date ) > 0, 1,
      DAYOFYEAR( @start_date ) ) )
         AND IF( YEAR( @end_date ) - YEAR( @start_date ) > 1, 366,
               IF( YEAR( @end_date ) - YEAR( @start_date ) > 0,
                 IF( DAYOFYEAR( @start_date ) <= DAYOFYEAR( @end_date ), 366,
                   DAYOFYEAR( @end_date ) ),
                 DAYOFYEAR( @end_date ) ) );