help we write this SQL weekday query
-
I need a query that will give me the number of weekdays for a given period of time.
For instance, how many week days are there between 2006-01-01 and today.
thanks for any help that you can provide!
-
select datediff(dd,'2006-01-01', getdate()) gives you the number of days.
If you want weekdays(mo-fr) you would have to make your own function like this one :
CREATE FUNCTION dbo.udf_convert_date2WeekRange (@RefDate datetime, @FirstLast char(1) = 'F')
RETURNS datetime
AS
BEGIN
DECLARE @return_date as datetime
declare @firstdate datetime
declare @WrkDate datetime
declare @refDays int
select @WrkDate = cast(convert(char(10), @RefDate, 121) as datetime)
select @refDays = (datepart(dw,@WrkDate) - 1) * (-1)
select @firstdate = dateadd( dd,@refDays, @WrkDate)
if @FirstLast = 'F'
begin
set @return_date = @firstdate
end
else
begin
select @return_date = dateadd(ms,-2,dateadd(dd,7,@Firstdate))
end
RETURN (@return_date)
END
go
--
Declare @Datum datetime
--set @Datum = getdate()
set @Datum = '2005/01/04'
select @Datum as Datum, datepart(ww,@Datum) as WeekNr, dbo.udf_convert_date2WeekRange(@Datum,'F') as WeekStartDate, dbo.udf_convert_date2WeekRange(@Datum,'L') as WeekEndDate
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Not my finest bit of code but...
set nocount on
declare @startdate datetime
declare @table table (DW int)
set @startdate = '1 Jan 2006'
while @startdate < getdate()
begin
insert into @table (DW)
values (datepart(dw,@startdate))
select @startdate = dateadd(dd,1,@startdate)
end
select NOWorkDays = count(*) from @table where DW not in (1,7)
as long as as you have the first day of the week set to Sunday.
S
-
SET DATEFIRST 1
SELECT (DATEDIFF(week,'20060101',GETDATE())*5)+DATEPART(weekday,GETDATE())
providing it is not run on a Sat/Sun
Far away is close at hand in the images of elsewhere.
Anon. -
thanks for your help
i spoke with our dba and he said that it did not have to be exact. he suggested something like:
select datediff(dd,'2006-01-01', getdate()) / 7 * 5
-
Have a go at this.
if exists (select name from sysobjects where name='trx_workdays' and type='FN') drop function trx_workdays
go
create function trx_workdays(@p_startdate datetime, @p_enddate datetime) returns integer as
begin
declare @dwdest int
declare @padded_enddate datetime
declare @padded_workdays int
declare @DW int
declare @diff int
if @p_startdate is null or @p_enddate is null
return 0
set @padded_enddate=@p_enddate
set @padded_workdays=0
-- pad end date so that difference becomes a multiple of 7 days;
-- we also need to count the number of weekdays in the days we added for the padding;
set @dwdest=datepart(dw,@p_startdate)-1
if @dwdest=0 set @dwdest=7
while datepart(dw,@padded_enddate)<>@dwdest -- loops no more than 6 times
begin
set @padded_enddate=dateadd(d,1,@padded_enddate)
set @DW=datepart(dw,@padded_enddate)
-- 1=saturday 7=sunday
if @DW<>1 and @DW<>7 set @padded_workdays=@padded_workdays+1
end
set @diff=datediff(d,@p_startdate,@padded_enddate)+1
-- subtract number of intervening saturdays/sundays and subtract number of workdays we added for the padding
return @diff-@diff/7*2-@padded_workdays
end
go
grant all on trx_workdays to public
go
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply