declare @dateFrom smalldatetime,@dateTo smalldatetime
set @dateFrom='2009-1-10'
set @dateTo='2009-1-14'declare @days int
set @days=0set datefirst 1
while @dateFrom<@dateTo
begin
if datepart(dw,@dateFrom)<>7
set @days=@days+1
set @dateFrom=dateadd(day,1,@dateFrom)
endselect @days as days
/*
days
-----------
3
*/
set @dateFrom='2009-1-10'
set @dateTo='2009-1-14'declare @days int
set @days=0set datefirst 1
while @dateFrom<@dateTo
begin
if datepart(dw,@dateFrom)<>7
set @days=@days+1
set @dateFrom=dateadd(day,1,@dateFrom)
endselect @days as days
/*
days
-----------
3
*/
--try:declare @x datetime,@y datetime
set @x='2009-01-10'
set @y='2009-01-19'select datediff(day,@x,@y)-datediff(week,@x,@y)
SELECT DATEPART(DW, "周日")来实现,或者通过set datefirst来将周日定位一周的第一天
DECLARE @DayCount INT
DECLARE @CurrentDate NVARCHAR(10)SET @DayCount = 0
SELECT @CurrentDate = CONVERT(NVARCHAR(10), @startdate, 120) --这里将你的日期转换为只到天的格式
SELECT @endate = CONVERT(NVARCHAR(10), endate, 120)
WHILE @CurrentDate <> @endate
BEGIN
IF SELECT DATEPART(DW, @CurrentDate) <> 1
BEGIN
SET @DayCount = @DayCount + 1
END
SET @CurrentDate = CONVERT(NVARCHAR(10), DATEADD(DAY, 1, CAST(@CurrentDate AS DATETIME)), 12O)
END
set @y='2009-01-19'
set @x='2009-01-10'
set @y='2009-01-18'
--应该7天
declare @x datetime,@y datetime
set @x='2009-01-10'
set @y='2009-01-18'select datediff(day,@x,@y)-datediff(week,@x,@y)
/*
6
*/
set @dateFrom='2009-1-10'
set @dateTo='2009-1-18'declare @days int
set @days=0set datefirst 1
while @dateFrom<=@dateTo --少个等号
begin
if datepart(dw,@dateFrom)<>7
set @days=@days+1
set @dateFrom=dateadd(day,1,@dateFrom)
endselect @days as days
/*
days
-----------
7
*/
set @x='2009-01-10'
set @y='2009-01-18'
select datediff(day,@x,@y)-(cast(datename(week,@y)as int)-cast(datename(week,@x)as int))
-----
thanks csdyyr and szx1999