如何将一个 SQL 表中的日期与另一个表中定义的范围进行比较?

问题描述:

我有一张桌子用来存放活动和日期:

I have one table holding events and dates:

 NAME  | DOB
 -------------------
 Adam  | 6/26/1999
 Barry | 7/18/2005
 Daniel| 1/18/1984

我有另一个表格将日期范围定义为开始时间或结束时间,每个表格都有一个描述性代码:

I have another table defining date ranges as either start or end times, each with a descriptive code:

 CODE      | DATE
 ---------------------
 YearStart| 6/28/2013
 YearEnd  | 8/14/2013

我正在尝试编写 SQL 来查找第二个表中描述的时间的开始和结束之间的所有出生日期.YearStart 总是在六月,YearEnd 总是在八月.我想尝试:

I am trying to write SQL that will find all Birthdates that fall between the start and end of the times described in the second table. The YearStart will always be in June, and the YearEnd will always be in August. My thought was to try:

 SELECT 
      u.Name
      CAST(MONTH(u.DOB) AS varchar) + '/' + CAST(DAY(u.DOB) AS varchar) as 'Birthdate',
      u.DOB as 'Birthday'
 FROM 
      Users u
 WHERE
      MONTH(DOB) = '7' OR
      (MONTH(DOB) = '6' AND DAY(DOB) >= DAY(SELECT d.Date FROM Dates d WHERE d.Code='YearStart')) OR
      (MONTH(DOB) = '8' AND DAY(DOB) <= DAY(SELECT d.Date FROM Dates d WHERE d.Code='YearEnd')))
 ORDER BY
      MONTH(DOB) ASC, DAY(DOB) ASC

但这并没有通过,我猜是因为不能保证内部 SELECT 语句将只返回一行,因此无法解析为日期时间.我实际上如何完成此查询?

But this doesn't pass, I'm guessing because there is no guarantee that the internal SELECT statement will return only one row, so cannot be parsed as a datetime. How do I actually accomplish this query?

这看起来很奇怪,我仍然觉得我们缺少相关的要求,但请看以下内容.根据您的描述,年份似乎无关紧要,您希望生日介于给定的月/日之间.

This seems strange and I still feel like we're missing a relevant piece of the requirements, but look at the following. It seems from your description that the years are irrelevant and you want birthdays that fall between the given months/days.

SELECT 
    t1.Name, t1.DOB
FROM 
    t1
    JOIN t2 AS startDate ON (startDate.Code = 'YearStart')
    JOIN t2 AS endDate ON (endDate.Code = 'YearEnd')
WHERE 
    STUFF(CONVERT(varchar, t1.DOB, 112), 1, 4, '') BETWEEN 
        STUFF(CONVERT(varchar, startDate.[Date], 112), 1, 4, '')
        AND
        STUFF(CONVERT(varchar, endDate.[Date], 112), 1, 4, '')