Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 number of working days between two dates

Author  Topic 

cardgunner

326 Posts

Posted - 2008-07-31 : 09:52:40
I'm not sure if the answer to this lies in the F_TABLE_DATE Function or not.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

I looked at the function, but I have NO idea what I'm looking at and what it does.

I have two dates a start date(@s_date) and end date(@e_date). I would like to know the number of working days that are between these two dates. I want to exclude Saturdays and Sundays and Some of the American Federal Holidays.

I say some because we are open and count some as working days and some not.

I can give some dates like New Years Day is always 1/1/???? but Thanksgiving is 11/??/????.

But in any case is my answer to finding the number of working days between two dates in the function above or is there something less complex that I may be able to grasp?

CardGunner

CardGunner

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-31 : 09:59:32
This would help u,
if there any issues let me know.


DECLARE @StartDate DATETIME,
@EndDate DATETIME
SELECT @StartDate = '01-July-2008',
@EndDate = '30-July-2008'
;WITH DATE (Date1)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000101')
UNION ALL
SELECT DATEADD(DAY, 1, Date1)
FROM DATE
WHERE Date1 < @EndDate
)
SELECT
CONVERT(VARCHAR(15),d1.DATE1 ,110) as [Working Date],
DATENAME(weekday, d1.Date1) [Working Day]
from DATE d1 where (DATENAME(weekday, d1.Date1)) not in ('Saturday','Sunday')
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 10:08:56

SELECT COUNT(*)
FROM F_TABLE_DATE(@s_date, @e_date) d
left JOIN holidays h ON d.[DATE] = h.[DATE]
WHERE h.[DATE] IS NULL
AND d.[DAY_OF_WEEK] BETWEEN 2 AND 6



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

cardgunner

326 Posts

Posted - 2008-07-31 : 10:14:48
Thanks VG for the quick reply.

I took what you had and posted it in my SQl analyer and the following error came up Incorrect syntax near the keyword 'WITH'.

I tried taking it out ; and replacing it with , but no luck.

I'm pretty new to this so if I;m overlooking the obvious excuse me.


CardGunner
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 10:16:19
quote:
Originally posted by cardgunner

Thanks VG for the quick reply.

I took what you had and posted it in my SQl analyer and the following error came up Incorrect syntax near the keyword 'WITH'.

I tried taking it out ; and replacing it with , but no luck.

I'm pretty new to this so if I;m overlooking the obvious excuse me.


CardGunner



That code is for SQL Server 2005. Are you using 2005 or 2000 ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-31 : 10:16:45
VGuyz solution will not work on SQL2000 as it uses a CTE

Em
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-07-31 : 10:18:45
quote:
Originally posted by khtan


SELECT COUNT(*)
FROM F_TABLE_DATE(@s_date, @e_date) d
left JOIN holidays h ON d.[DATE] = h.[DATE]
WHERE h.[DATE] IS NULL
AND d.[DAY_OF_WEEK] BETWEEN 2 AND 6



KH
[spoiler]Time is always against us[/spoiler]





Thanks fior the reply KH.

I take it my answer is in the F_TABLE_DATE function?

Most of that TO ME is chinese.

Can I copy it and paste into my SQL analyzer, run it and it will work?

CardGunner
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-07-31 : 10:21:42
I found in excel there is a networkday function. =networkday(Date1, date2, holidays) and that worked fine for me in Excel. Does not work in SQL.

CardGunner
Go to Top of Page

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-31 : 10:46:11
Hi cardgunner,

really sorry ya,
i have not think abt sql server 2005 or 2000

chk it out this query



SELECT CONVERT(VARCHAR(15),DATEADD(d,number,getdate()),110) as [Working Date],
DATENAME(weekday, DATEADD(d,number,getdate()))as [Working Day]
FROM master..spt_values
WHERE type='p'
AND DATEPART(dw,DATEADD(d,number,getdate())) NOT IN (1,7)
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 10:49:47
quote:
Originally posted by cardgunner

I found in excel there is a networkday function. =networkday(Date1, date2, holidays) and that worked fine for me in Excel. Does not work in SQL.

CardGunner



Of-course it will not.


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 10:50:31
quote:
Can I copy it and paste into my SQL analyzer, run it and it will work?

Can. What's stopping you ? Just try . . . . it won't bite


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 11:03:57
It you can calculate the number of days, that would be better than counting the days.
Try this
create function workhours_jeff(@StartDate datetime, @EndDate datetime) returns float as
begin

return CAST(
(
(DATEDIFF(dd,@StartDate,@EndDate)+1)
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END)
)
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' or DATENAME(dw,@StartDate) = 'Saturday' THEN 0
ELSE CAST(CONVERT(CHAR(12),@StartDate,114) AS DATETIME) END)
-(CASE WHEN DATENAME(dw,@EndDate) = 'Sunday' or DATENAME(dw,@EndDate) = 'Saturday' THEN 0
ELSE (1.0-CAST(CONVERT(CHAR(12),@EndDate,114) AS DATETIME)) END)
AS FLOAT) * 24
end
go



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-07-31 : 11:18:39
Ok

I copied the code below and ran it. I got some errors

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.

Also I need to declare @FIRST_DATE, @LAST_DATE, @start_date, @end_date, and @DATE

Server: Msg 208, Level 16, State 11, Line 2
Invalid object name 'dbo.F_TABLE_DATE'.

Please be patient I have no idea exactly what a function is and a little idea what it does but unsure how it does it.

Also I have very little idea of what I'm doing.

 returns  @DATE table 
(
[DATE_ID] [int] not null
primary key clustered,
[DATE] [datetime] not null ,
[NEXT_DAY_DATE] [datetime] not null ,
[YEAR] [smallint] not null ,
[YEAR_QUARTER] [int] not null ,
[YEAR_MONTH] [int] not null ,
[YEAR_DAY_OF_YEAR] [int] not null ,
[QUARTER] [tinyint] not null ,
[MONTH] [tinyint] not null ,
[DAY_OF_YEAR] [smallint] not null ,
[DAY_OF_MONTH] [smallint] not null ,
[DAY_OF_WEEK] [tinyint] not null ,

[YEAR_NAME] [varchar] (4) not null ,
[YEAR_QUARTER_NAME] [varchar] (7) not null ,
[YEAR_MONTH_NAME] [varchar] (8) not null ,
[YEAR_MONTH_NAME_LONG] [varchar] (14) not null ,
[QUARTER_NAME] [varchar] (2) not null ,
[MONTH_NAME] [varchar] (3) not null ,
[MONTH_NAME_LONG] [varchar] (9) not null ,
[WEEKDAY_NAME] [varchar] (3) not null ,
[WEEKDAY_NAME_LONG] [varchar] (9) not null ,

[START_OF_YEAR_DATE] [datetime] not null ,
[END_OF_YEAR_DATE] [datetime] not null ,
[START_OF_QUARTER_DATE] [datetime] not null ,
[END_OF_QUARTER_DATE] [datetime] not null ,
[START_OF_MONTH_DATE] [datetime] not null ,
[END_OF_MONTH_DATE] [datetime] not null ,

[START_OF_WEEK_STARTING_SUN_DATE] [datetime] not null ,
[END_OF_WEEK_STARTING_SUN_DATE] [datetime] not null ,
[START_OF_WEEK_STARTING_MON_DATE] [datetime] not null ,
[END_OF_WEEK_STARTING_MON_DATE] [datetime] not null ,
[START_OF_WEEK_STARTING_TUE_DATE] [datetime] not null ,
[END_OF_WEEK_STARTING_TUE_DATE] [datetime] not null ,
[START_OF_WEEK_STARTING_WED_DATE] [datetime] not null ,
[END_OF_WEEK_STARTING_WED_DATE] [datetime] not null ,
[START_OF_WEEK_STARTING_THU_DATE] [datetime] not null ,
[END_OF_WEEK_STARTING_THU_DATE] [datetime] not null ,
[START_OF_WEEK_STARTING_FRI_DATE] [datetime] not null ,
[END_OF_WEEK_STARTING_FRI_DATE] [datetime] not null ,
[START_OF_WEEK_STARTING_SAT_DATE] [datetime] not null ,
[END_OF_WEEK_STARTING_SAT_DATE] [datetime] not null ,

[QUARTER_SEQ_NO] [int] not null ,
[MONTH_SEQ_NO] [int] not null ,

[WEEK_STARTING_SUN_SEQ_NO] [int] not null ,
[WEEK_STARTING_MON_SEQ_NO] [int] not null ,
[WEEK_STARTING_TUE_SEQ_NO] [int] not null ,
[WEEK_STARTING_WED_SEQ_NO] [int] not null ,
[WEEK_STARTING_THU_SEQ_NO] [int] not null ,
[WEEK_STARTING_FRI_SEQ_NO] [int] not null ,
[WEEK_STARTING_SAT_SEQ_NO] [int] not null ,

[JULIAN_DATE] [int] not null ,
[MODIFIED_JULIAN_DATE] [int] not null ,

[ISO_DATE] [varchar](10) not null ,
[ISO_YEAR_WEEK_NO] [int] not null ,
[ISO_WEEK_NO] [smallint] not null ,
[ISO_DAY_OF_WEEK] [tinyint] not null ,
[ISO_YEAR_WEEK_NAME] [varchar](8) not null ,
[ISO_YEAR_WEEK_DAY_OF_WEEK_NAME] [varchar](10) not null ,

[DATE_FORMAT_YYYY_MM_DD] [varchar](10) not null ,
[DATE_FORMAT_YYYY_M_D] [varchar](10) not null ,
[DATE_FORMAT_MM_DD_YYYY] [varchar](10) not null ,
[DATE_FORMAT_M_D_YYYY] [varchar](10) not null ,
[DATE_FORMAT_MMM_D_YYYY] [varchar](12) not null ,
[DATE_FORMAT_MMMMMMMMM_D_YYYY] [varchar](18) not null ,
[DATE_FORMAT_MM_DD_YY] [varchar](8) not null ,
[DATE_FORMAT_M_D_YY] [varchar](8) not null
)
as
begin
declare @cr varchar(2)
select @cr = char(13)+Char(10)
declare @ErrorMessage varchar(400)
declare @START_DATE datetime
declare @END_DATE datetime
declare @LOW_DATE datetime

declare @start_no int
declare @end_no int

-- Verify @FIRST_DATE is not null
if @FIRST_DATE is null
begin
select @ErrorMessage =
'@FIRST_DATE cannot be null'
goto Error_Exit
end

-- Verify @LAST_DATE is not null
if @LAST_DATE is null
begin
select @ErrorMessage =
'@LAST_DATE cannot be null'
goto Error_Exit
end

-- Verify @FIRST_DATE is not before 1754-01-01
IF @FIRST_DATE < '17540101' begin
select @ErrorMessage =
'@FIRST_DATE cannot before 1754-01-01'+
', @FIRST_DATE = '+
isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')
goto Error_Exit
end

-- Verify @LAST_DATE is not after 9997-12-31
IF @LAST_DATE > '99971231' begin
select @ErrorMessage =
'@LAST_DATE cannot be after 9997-12-31'+
', @LAST_DATE = '+
isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
goto Error_Exit
end

-- Verify @FIRST_DATE is not after @LAST_DATE
if @FIRST_DATE > @LAST_DATE
begin
select @ErrorMessage =
'@FIRST_DATE cannot be after @LAST_DATE'+
', @FIRST_DATE = '+
isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')+
', @LAST_DATE = '+
isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
goto Error_Exit
end

-- Set @START_DATE = @FIRST_DATE at midnight
select @START_DATE = dateadd(dd,datediff(dd,0,@FIRST_DATE),0)
-- Set @END_DATE = @LAST_DATE at midnight
select @END_DATE = dateadd(dd,datediff(dd,0,@LAST_DATE),0)
-- Set @LOW_DATE = earliest possible SQL Server datetime
select @LOW_DATE = convert(datetime,'17530101')

-- Find the number of day from 1753-01-01 to @START_DATE and @END_DATE
select @start_no = datediff(dd,@LOW_DATE,@START_DATE) ,
@end_no = datediff(dd,@LOW_DATE,@END_DATE)

-- Declare number tables
declare @num1 table (NUMBER int not null primary key clustered)
declare @num2 table (NUMBER int not null primary key clustered)
declare @num3 table (NUMBER int not null primary key clustered)

-- Declare table of ISO Week ranges
declare @ISO_WEEK table
(
[ISO_WEEK_YEAR] int not null
primary key clustered,
[ISO_WEEK_YEAR_START_DATE] datetime not null,
[ISO_WEEK_YEAR_END_DATE] Datetime not null
)

-- Find rows needed in number tables
declare @rows_needed int
declare @rows_needed_root int
select @rows_needed = @end_no - @start_no + 1
select @rows_needed =
case
when @rows_needed < 10
then 10
else @rows_needed
end
select @rows_needed_root = convert(int,ceiling(sqrt(@rows_needed)))

-- Load number 0 to 16
insert into @num1 (NUMBER)
select NUMBER = 0 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15
order by
1
-- Load table with numbers zero thru square root of the number of rows needed +1
insert into @num2 (NUMBER)
select
NUMBER = a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER)
from
@num1 a cross join @num1 b cross join @num1 c
where
a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER) <
@rows_needed_root
order by
1

-- Load table with the number of rows needed for the date range
insert into @num3 (NUMBER)
select
NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER)
from
@num2 a
cross join
@num2 b
where
a.NUMBER+(@rows_needed_root*b.NUMBER) < @rows_needed
order by
1

declare @iso_start_year int
declare @iso_end_year int

select @iso_start_year = datepart(year,dateadd(year,-1,@start_date))
select @iso_end_year = datepart(year,dateadd(year,1,@end_date))

-- Load table with start and end dates for ISO week years
insert into @ISO_WEEK
(
[ISO_WEEK_YEAR],
[ISO_WEEK_YEAR_START_DATE],
[ISO_WEEK_YEAR_END_DATE]
)
select
[ISO_WEEK_YEAR] = a.NUMBER,
[0ISO_WEEK_YEAR_START_DATE] =
dateadd(dd,(datediff(dd,@LOW_DATE,
dateadd(day,3,dateadd(year,a.[NUMBER]-1900,0))
)/7)*7,@LOW_DATE),
[ISO_WEEK_YEAR_END_DATE] =
dateadd(dd,-1,dateadd(dd,(datediff(dd,@LOW_DATE,
dateadd(day,3,dateadd(year,a.[NUMBER]+1-1900,0))
)/7)*7,@LOW_DATE))
from
(
select
NUMBER = NUMBER+@iso_start_year
from
@num3
where
NUMBER+@iso_start_year <= @iso_end_year
) a
order by
a.NUMBER

-- Load Date table
insert into @DATE
select
[DATE_ID] = a.[DATE_ID] ,
[DATE] = a.[DATE] ,

[NEXT_DAY_DATE] =
dateadd(day,1,a.[DATE]) ,

[YEAR] =
datepart(year,a.[DATE]) ,
[YEAR_QUARTER] =
(10*datepart(year,a.[DATE]))+datepart(quarter,a.[DATE]) ,

[YEAR_MONTH] =
(100*datepart(year,a.[DATE]))+datepart(month,a.[DATE]) ,
[YEAR_DAY_OF_YEAR] =
(1000*datepart(year,a.[DATE]))+
datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 ,
[QUARTER] =
datepart(quarter,a.[DATE]) ,
[MONTH] =
datepart(month,a.[DATE]) ,
[DAY_OF_YEAR] =
datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 ,
[DAY_OF_MONTH] =
datepart(day,a.[DATE]) ,
[DAY_OF_WEEK] =
-- Sunday = 1, Monday = 2, ,,,Saturday = 7
(datediff(dd,'17530107',a.[DATE])%7)+1 ,
[YEAR_NAME] =
datename(year,a.[DATE]) ,
[YEAR_QUARTER_NAME] =
datename(year,a.[DATE])+' Q'+datename(quarter,a.[DATE]) ,
[YEAR_MONTH_NAME] =
datename(year,a.[DATE])+' '+left(datename(month,a.[DATE]),3) ,
[YEAR_MONTH_NAME_LONG] =
datename(year,a.[DATE])+' '+datename(month,a.[DATE]) ,
[QUARTER_NAME] =
'Q'+datename(quarter,a.[DATE]) ,
[MONTH_NAME] =
left(datename(month,a.[DATE]),3) ,
[MONTH_NAME_LONG] =
datename(month,a.[DATE]) ,
[WEEKDAY_NAME] =
left(datename(weekday,a.[DATE]),3) ,
[WEEKDAY_NAME_LONG] =
datename(weekday,a.[DATE]),

[START_OF_YEAR_DATE] =
dateadd(year,datediff(year,0,a.[DATE]),0) ,
[END_OF_YEAR_DATE] =
dateadd(day,-1,dateadd(year,datediff(year,0,a.[DATE])+1,0)) ,

[START_OF_QUARTER_DATE] =
dateadd(quarter,datediff(quarter,0,a.[DATE]),0) ,
[END_OF_QUARTER_DATE] =
dateadd(day,-1,dateadd(quarter,datediff(quarter,0,a.[DATE])+1,0)) ,

[START_OF_MONTH_DATE] =
dateadd(month,datediff(month,0,a.[DATE]),0) ,
[END_OF_MONTH_DATE] =
dateadd(day,-1,dateadd(month,datediff(month,0,a.[DATE])+1,0)),

[START_OF_WEEK_STARTING_SUN_DATE] =
dateadd(dd,(datediff(dd,'17530107',a.[DATE])/7)*7,'17530107'),
[END_OF_WEEK_STARTING_SUN_DATE] =
dateadd(dd,((datediff(dd,'17530107',a.[DATE])/7)*7)+6,'17530107'),

[START_OF_WEEK_STARTING_MON_DATE] =
dateadd(dd,(datediff(dd,'17530101',a.[DATE])/7)*7,'17530101'),
[END_OF_WEEK_STARTING_MON_DATE] =
dateadd(dd,((datediff(dd,'17530101',a.[DATE])/7)*7)+6,'17530101'),

[START_OF_WEEK_STARTING_TUE_DATE] =
dateadd(dd,(datediff(dd,'17530102',a.[DATE])/7)*7,'17530102'),
[END_OF_WEEK_STARTING_TUE_DATE] =
dateadd(dd,((datediff(dd,'17530102',a.[DATE])/7)*7)+6,'17530102'),

[START_OF_WEEK_STARTING_WED_DATE] =
dateadd(dd,(datediff(dd,'17530103',a.[DATE])/7)*7,'17530103'),
[END_OF_WEEK_STARTING_WED_DATE] =
dateadd(dd,((datediff(dd,'17530103',a.[DATE])/7)*7)+6,'17530103'),

[START_OF_WEEK_STARTING_THU_DATE] =
dateadd(dd,(datediff(dd,'17530104',a.[DATE])/7)*7,'17530104'),
[END_OF_WEEK_STARTING_THU_DATE] =
dateadd(dd,((datediff(dd,'17530104',a.[DATE])/7)*7)+6,'17530104'),

[START_OF_WEEK_STARTING_FRI_DATE] =
dateadd(dd,(datediff(dd,'17530105',a.[DATE])/7)*7,'17530105'),
[END_OF_WEEK_STARTING_FRI_DATE] =
dateadd(dd,((datediff(dd,'17530105',a.[DATE])/7)*7)+6,'17530105'),

[START_OF_WEEK_STARTING_SAT_DATE] =
dateadd(dd,(datediff(dd,'17530106',a.[DATE])/7)*7,'17530106'),
[END_OF_WEEK_STARTING_SAT_DATE] =
dateadd(dd,((datediff(dd,'17530106',a.[DATE])/7)*7)+6,'17530106'),

[QUARTER_SEQ_NO] =
datediff(quarter,@LOW_DATE,a.[DATE]),
[MONTH_SEQ_NO] =
datediff(month,@LOW_DATE,a.[DATE]),

[WEEK_STARTING_SUN_SEQ_NO] =
datediff(day,'17530107',a.[DATE])/7,
[WEEK_STARTING_MON_SEQ_NO] =
datediff(day,'17530101',a.[DATE])/7,
[WEEK_STARTING_TUE_SEQ_NO] =
datediff(day,'17530102',a.[DATE])/7,
[WEEK_STARTING_WED_SEQ_NO] =
datediff(day,'17530103',a.[DATE])/7,
[WEEK_STARTING_THU_SEQ_NO] =
datediff(day,'17530104',a.[DATE])/7,
[WEEK_STARTING_FRI_SEQ_NO] =
datediff(day,'17530105',a.[DATE])/7,
[WEEK_STARTING_SAT_SEQ_NO] =
datediff(day,'17530106',a.[DATE])/7,

[JULIAN_DATE] =
datediff(day,@LOW_DATE,a.[DATE])+2361331,
[MODIFIED_JULIAN_DATE] =
datediff(day,'18581117',a.[DATE]),
--/*

[ISO_DATE] =
replace(convert(char(10),a.[DATE],111),'/','-') ,

[ISO_YEAR_WEEK_NO] =
(100*b.[ISO_WEEK_YEAR])+
(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 ,

[ISO_WEEK_NO] =
(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 ,

[ISO_DAY_OF_WEEK] =
-- Sunday = 1, Monday = 2, ,,,Saturday = 7
(datediff(dd,@LOW_DATE,a.[DATE])%7)+1 ,

[ISO_YEAR_WEEK_NAME] =
convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+
right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) ,

[ISO_YEAR_WEEK_DAY_OF_WEEK_NAME] =
convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+
right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) +
'-'+convert(varchar(1),(datediff(dd,@LOW_DATE,a.[DATE])%7)+1) ,
--*/
[DATE_FORMAT_YYYY_MM_DD] =
convert(char(10),a.[DATE],111) ,
[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))),
[DATE_FORMAT_MM_DD_YYYY] =
convert(char(10),a.[DATE],101) ,
[DATE_FORMAT_M_D_YYYY] =
convert(varchar(10),
convert(varchar(2),month(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
convert(varchar(4),year(a.[DATE]))),
[DATE_FORMAT_MMM_D_YYYY] =
convert(varchar(12),
left(datename(month,a.[DATE]),3)+' '+
convert(varchar(2),day(a.[DATE]))+', '+
convert(varchar(4),year(a.[DATE]))),
[DATE_FORMAT_MMMMMMMMM_D_YYYY] =
convert(varchar(18),
datename(month,a.[DATE])+' '+
convert(varchar(2),day(a.[DATE]))+', '+
convert(varchar(4),year(a.[DATE]))),
[DATE_FORMAT_MM_DD_YY] =
convert(char(8),a.[DATE],1) ,
[DATE_FORMAT_M_D_YY] =
convert(varchar(8),
convert(varchar(2),month(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
right(convert(varchar(4),year(a.[DATE])),2))
from
(
-- Derived table is all dates needed for date range
select top 100 percent
[DATE_ID] = aa.[NUMBER],
[DATE] =
dateadd(dd,aa.[NUMBER],@LOW_DATE)
from
(
select
NUMBER = NUMBER+@start_no
from
@num3
where
NUMBER+@start_no <= @end_no
) aa
order by
aa.[NUMBER]
) a
join
-- Match each date to the proper ISO week year
@ISO_WEEK b
on a.[DATE] between
b.[ISO_WEEK_YEAR_START_DATE] and
b.[ISO_WEEK_YEAR_END_DATE]
order by
a.[DATE_ID]

return

Error_Exit:

-- Return a pesudo error message by trying to
-- convert an error message string to an int.
-- This method is used because the error displays
-- the string it was trying to convert, and so the
-- calling application sees a formatted error message.

declare @error int

set @error = convert(int,@cr+@cr+
'*******************************************************************'+@cr+
'* Error in function F_TABLE_DATE:'+@cr+'* '+
isnull(@ErrorMessage,'Unknown Error')+@cr+
'*******************************************************************'+@cr+@cr)

return

end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT SELECT ON [dbo].[F_TABLE_DATE] TO [public]
GO
set dateformat ydm
go
print 'Checksum with ydm'
go
select
[Checksum] = checksum_agg(binary_checksum(*))
from
dbo.F_TABLE_DATE ( '20000101','20101231' )
go
set dateformat ymd
go
print 'Checksum with ymd'
go
select
[Checksum] = checksum_agg(binary_checksum(*))
from
dbo.F_TABLE_DATE ( '20000101','20101231' )
go
set dateformat ymd
go
-- Sample select for date range
select *
from
dbo.F_TABLE_DATE ( '20000101','20101231' )
order by 1



CardGunner
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 11:23:23
http://www.sqlservercentral.com/articles/Advanced+Querying/howmanymoremondaysuntiliretire/2475/



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-07-31 : 11:50:14
Sorry I didn't bring in the first part so I copied now from

if exists (select * from dbo.sysobjects

to

dbo.F_TABLE_DATE ( '20000101','20101231' )
order by 1

and I get the following errors:
Server: Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 472
Must declare the variable '@start_date'.
Server: Msg 137, Level 15, State 1, Procedure F_TABLE_DATE, Line 473
Must declare the variable '@end_date'.
Server: Msg 208, Level 16, State 11, Line 2
Invalid object name 'dbo.F_TABLE_DATE'.
Checksum with ydm
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Checksum with ymd
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.F_TABLE_DATE'.


CardGunner
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-07-31 : 13:36:19
Wow!

Thanks KH(Khtan) for the nudge.

I changed @start_date and @end_date to @START_DATE and @END_DATE and everyhting ran.

I then took your select statement and added my info as below and it came up with 120 days which should be accurate.

 declare @s_date datetime
declare @e_date datetime
select @s_date='2008-03-11 00:00:00.000'
select @e_date='2008-08-26 00:00:00.000'

SELECT COUNT(*)
FROM F_TABLE_DATE(@s_date, @e_date) d left JOIN
(select '2008-7-04 00:00:00.000' [DATE]
) h ON d.[DATE] = h.[DATE]
WHERE h.[DATE] IS NULL
AND d.[DAY_OF_WEEK] BETWEEN 2 AND 6


I know what I did to get it but I don't know what it all means.

I looked and there is only a new function in my db right?

This is really great! Thank you very much.



CardGunner
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-31 : 14:40:21
I hope that you realize that the guery against F_TABLE_DATE is only giving you a count of weekdays.

If you want to exclude holidays, you will need to create a table with the holidays, and join to the output from your query to exclude holidays.

CODO ERGO SUM
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-07-31 : 15:18:04
Yup, that I got. I tested with just one date and it worked.

I may still have an issue cause I'm trying to find the number of working days from a datefield in a table to a set date. Where my results will be say 262 records long.

This works for two set dates @s_date and @e_date.

How do I do it for a 262 records?



CardGunner
Go to Top of Page

Corneliavnp
Starting Member

2 Posts

Posted - 2013-06-26 : 14:21:13
unspammed
Go to Top of Page
   

- Advertisement -