如何将一个 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, '')