计算一周中没有天数 [英] calculating no of days in a week

问题描述

我们如何计算一周内的确切天数

例如：第一周采取oct它实际上是5只有几天......在septober中的2天...我需要在查询计算时输出5

....帮帮我

解决方案

试试这段代码。希望这是你想要的。这将是一周的开始和结束日期。你用它来计算那一周的天数。

 选择 dateadd（DD， - （DATEPART（dw，GETDATE（） -  1）），GETDATE（））
，dateadd（DD，7-DATEPART（dw，GETDATE（）），GETDATE（））
 

我假设一周你指的是一个月中的一周，而不是一年中的一周。



例如，2013年10月的第1周有6天，2013年10月的第5周有4天。



如果是这样的话那么这样可能对你有用;

声明@year int 
声明@month int 
声明@week int 
声明@target datetime 
 
声明@firstDay int 
 
声明@startOfMonth datetime 
声明@endOfMonth datetime 
声明@startOfMonthsFirstWeek datetime 
声明@startOfWeek datetime 
声明@endOfWeek datetime 
声明@start datetime 
声明@end datetime 
 
  - 这三个是inp ut 
 set @ year = 2013 
 set @ month = 10 
 set @ week = 5 
 
 set @ startOfMonth = cast（cast（@year as varchar）+ ' - '+ cast（@month as varchar）+' -  1'as datetime）
 set @ endOfMonth = dateadd（day，-1，cast（cast（@year as varchar）+' - '+ cast（ @month + 1 as varchar）+' - '作为datetime））
 
 set @startOfMonthsFirstWeek = case datepart（weekday，@ startOfMonth）1然后@startOfMonth else dateadd（day，2 -datepart（工作日，@ startOfMonth），@ startOfMonth）结束
设置@startOfWeek = dateadd（day，7 *（@ week  -  1），@ startOfMonthsFirstWeek）
 set @endOfWeek = dateadd（day，7 * @week -  1，@ startOfMonthsFirstWeek）
 
选择@start = max（[Date]）从（选择@startOfMonth为[Date] union（选择@startOfWeek为[Date]））为[Start] 
选择@end = min（[Date]）从（选择@endOfWeek为[Date] union（选择@endOfMonth为[Date]））为[End] 
 
声明@numberOfDaysInMonthWeek int 
 set @numberOfDaysInMonthWeek = 1 + datediff（day，@ start，@ end）
 
选择@numberOfDaysInMonthWeek作为[NumberOfDaysInWeekOfMonth] 

希望这会有所帮助，

Fredrik

hi,

How we can calculate the exact no of days in a week
eg: take oct first week it actuall 5 days only ...2 days in septober...i need out put as 5
while querying for some calculation....help me

解决方案

Try this code. Hope this is what you want. This will start and end date of the week. You use it to calculate number of days in that week.

select dateadd(DD,-(DATEPART(dw, GETDATE()-1)),GETDATE())
,dateadd(DD,7-DATEPART(dw, GETDATE()),GETDATE())

I am assuming by week you mean the week in the month, not the week in the year.

So for example, week 1 in October 2013 has 6 days and week 5 in October 2013 has 4 days.

If that is the case then something like this might work for you;

declare @year int
declare @month int
declare @week int
declare @target datetime

declare @firstDay int

declare @startOfMonth datetime
declare @endOfMonth datetime
declare @startOfMonthsFirstWeek datetime
declare @startOfWeek datetime
declare @endOfWeek datetime
declare @start datetime
declare @end datetime

-- These three are the input
set @year=2013
set @month=10
set @week=5

set @startOfMonth=cast(cast(@year as varchar) + '-' + cast(@month as varchar) + '-1' as datetime)
set @endOfMonth=dateadd(day, -1, cast(cast(@year as varchar) + '-' + cast(@month + 1 as varchar) + '-1' as datetime))

set @startOfMonthsFirstWeek = case datepart(weekday, @startOfMonth) when 1 then @startOfMonth else dateadd(day, 2 -datepart(weekday, @startOfMonth), @startOfMonth) end
set @startOfWeek = dateadd(day, 7 * (@week - 1), @startOfMonthsFirstWeek)
set @endOfWeek = dateadd(day, 7 * @week - 1, @startOfMonthsFirstWeek)

select @start = max([Date]) from (select @startOfMonth as [Date] union (select @startOfWeek as [Date])) as [Start]
select @end = min([Date]) from (select @endOfWeek as [Date] union (select @endOfMonth as [Date])) as [End]

declare @numberOfDaysInMonthWeek int
set @numberOfDaysInMonthWeek = 1 + datediff(day, @start, @end)

select @numberOfDaysInMonthWeek as [NumberOfDaysInWeekOfMonth]

Hope this helps,
Fredrik

这篇关于计算一周中没有天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！