因此,对于开始日期和结束日期,我想确定这两个日期之间一周的特定日期.
星期一,星期二等等
我知道我可以在开始日期和结束日期之间循环并检查每一天,但差异可能是很多天.我更喜欢不需要循环的东西.有任何想法吗？ (必须在sql Server 2005中支持)
解决方法
鉴于我认为你想要得到的,这应该做到：
SET DATEFIRST 1
DECLARE
@start_date DATETIME,@end_date DATETIME
SET @start_date = '2011-07-11'
SET @end_date = '2011-07-22'
;WITH Days_Of_The_Week AS (
SELECT 1 AS day_number,'Monday' AS day_name UNION ALL
SELECT 2 AS day_number,'Tuesday' AS day_name UNION ALL
SELECT 3 AS day_number,'Wednesday' AS day_name UNION ALL
SELECT 4 AS day_number,'Thursday' AS day_name UNION ALL
SELECT 5 AS day_number,'Friday' AS day_name UNION ALL
SELECT 6 AS day_number,'Saturday' AS day_name UNION ALL
SELECT 7 AS day_number,'Sunday' AS day_name
)
SELECT
day_name,1 + DATEDIFF(wk,@start_date,@end_date) -
CASE WHEN DATEPART(weekday,@start_date) > day_number THEN 1 ELSE 0 END -
CASE WHEN DATEPART(weekday,@end_date) < day_number THEN 1 ELSE 0 END
FROM
Days_Of_The_Week
