declare @startDate datetime = '2016-01-13'
declare @endDate datetime = '2016-02-12'
;with dateRange as
(
select [Date] = dateadd(dd, 1, @startDate)
where dateadd(dd, 1, @startDate) < @endDate
union all
select dateadd(dd, 1, [Date])
from dateRange
where dateadd(dd, 1, [Date]) < @endDate
)
select [Date], datename(dw,[Date])
from dateRange
0
要根据你的评论计算每一天的数量（这应该是问题的一部分），改变詹姆斯答案的最后部分：
select datename(dw,[Date]) as day_name, count([Date]) as number_days
from dateRange group by datename(dw,[Date]), datepart(DW,[Date])
order by datepart(DW,[Date]);
0
你可以尝试这样的事情：
DECLARE @StartDate DATETIME
DECLARE @StartDateFixed DATETIME
DECLARE @EndDate DATETIME
DECLARE @NumberOfDays int
SET @StartDate = '2016/01/01'
SET @EndDate = '2016/01/02'
SET @NumberOfDays = DATEDIFF(DAY,@StartDate,@EndDate) + 1
SET @StartDateFixed = DATEADD(DD,-1,@StartDate)
SELECT WeekDay , COUNT(WeekDay)
FROM (
SELECT TOP (@NumberOfDays) WeekDay = DATENAME(DW , DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY spt.name), @StartDateFixed))
FROM [master].[dbo].[spt_values] spt
) A
GROUP BY WeekDay
DECLARE @nxt_mont INT = (SELECT MONTH(DATEADD(month, 1, GETDATE())))
下个月（如果是12月份的变化）
DECLARE @nxt_year INT = (SELECT YEAR(DATEADD(month, 1, GETDATE())))
创建开始日期
DECLARE @startDate DATETIME = (SELECT CAST(CAST(@cur_year AS varchar) + '-' + CAST(@cur_mont AS varchar) + '-' + CAST(13 AS varchar) AS DATETIME))
创建结束日期
DECLARE @endDate DATETIME = (SELECT CAST(CAST(@nxt_year AS varchar) + '-' + CAST(@nxt_mont AS varchar) + '-' + CAST(12 AS varchar) AS DATETIME))
开始日期和结束日期之间的日期
SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @startDate) AS Date,
DATENAME(DW, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @startDate)) AS Day
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
4
DECLARE @dayStart int = 13, --The day of current month
@dayEnd int = 12, --The day of another month
@howManyMonth int = 1, --How many month to take
@dateStart date,
@dateEnd date
--Here we determine range of the dates
SELECT @dateStart = CONVERT (date,
CAST(DATEPART(Year,GETDATE()) as nvarchar(5))+ '-' +
CASE WHEN LEN(CAST(DATEPART(Month,GETDATE()) as nvarchar(5))) = 1
THEN '0'+CAST(DATEPART(Month,GETDATE()) as nvarchar(5))
ELSE CAST(DATEPART(Month,GETDATE()) as nvarchar(5)) END + '-' +
CAST (@dayStart as nvarchar(5))),
@dateEnd = CONVERT (date,
CAST(DATEPART(Year,DATEADD(Month,@howManyMonth,GETDATE())) as nvarchar(5))+ '-' +
CASE WHEN LEN(CAST(DATEPART(Month,DATEADD(Month,@howManyMonth,GETDATE())) as nvarchar(5))) = 1
THEN '0'+CAST(DATEPART(Month,DATEADD(Month,@howManyMonth,GETDATE())) as nvarchar(5))
ELSE CAST(DATEPART(Month,DATEADD(Month,@howManyMonth,GETDATE())) as nvarchar(5)) END + '-' +
CAST (@dayEnd as nvarchar(5)))
;WITH cte AS (
SELECT @dateStart as date_
UNION ALL
SELECT DATEADD(day,1,date_)
FROM cte
WHERE DATEADD(day,1,date_) <= @dateEnd
)
--Get results
SELECT DATENAME(WEEKDAY,date_) as [DayOfWeek],
COUNT(*) as [DaysCount]
FROM cte
GROUP BY DATEPART(WEEKDAY,date_),
DATENAME(WEEKDAY,date_)
ORDER BY DATEPART(WEEKDAY,date_)
OPTION (MAXRECURSION 0)
5 回答
试试这个：
要根据你的评论计算每一天的数量（这应该是问题的一部分），改变詹姆斯答案的最后部分：
你可以尝试这样的事情：
输出将是
如果您需要从指定的日期编号获取当前和下一个日期，例如13和12
这个月
今年
下个月
下个月（如果是12月份的变化）
创建开始日期
创建结束日期
开始日期和结束日期之间的日期
输出：