如何获取两个日期之间的星期一、星期二等

Question

大家好，我正在尝试使用how to calculate the number of Tuesdays between two dates in TSQL算法来计算两个日期之间出现的天数。

我只是用我的日期替换了一些参数，但是我得到了语法错误。我尝试删除datetime以使用date，但仍然收到错误

select 

datediff(day, -7, DATETIME('2015-06-30'))/7-datediff(day, -6,   DATETIME('2015-05-28'))/7 AS MON,

datediff(day, -6, DATETIME('2015-06-30'))/7-datediff(day, -5,DATETIME('2015-05-28'))/7 AS TUE,
datediff(day, -5,DATETIME('2015-06-30'))/7-datediff(day, -4, DATETIME('2015-05-28'))/7 AS WED,
datediff(day, -4, DATETIME('2015-06-30'))/7-datediff(day, -3, DATETIME('2015-05-28'))/7 AS THU,
datediff(day, -3,DATETIME('2015-06-30'))/7-datediff(day, -2,DATETIME('2015-05-28'))/7 AS FRI,
datediff(day, -2, DATETIME('2015-06-30'))/7-datediff(day, -1,DATETIME('2015-05-28'))/7 AS SAT,
datediff(day, -1,DATETIME('2015-06-30'))/7-datediff(day, 0, DATETIME('2015-05-28'))/7 AS SUN

Answer 1

您使用了错误的转换语法

我修改了Sql查询，如下所示

select datediff(day, -7, CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -6,  CONVERT(DATETIME,'2015-05-28'))/7 AS MON,
datediff(day, -6, CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -5,CONVERT(DATETIME,'2015-05-28'))/7 AS TUE,
datediff(day, -5,CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -4, CONVERT(DATETIME,'2015-05-28'))/7 AS WED,
datediff(day, -4, CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -3,CONVERT(DATETIME,'2015-05-28'))/7 AS THU,
datediff(day, -3,CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -2,CONVERT(DATETIME,'2015-05-28'))/7 AS FRI,
datediff(day, -2, CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -1,CONVERT(DATETIME,'2015-05-28'))/7 AS SAT,
datediff(day, -1,CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, 0, CONVERT(DATETIME,'2015-05-28'))/7 AS SUN

谢谢