?
Solved

Calculate working date & time using one date

Posted on 2010-01-11
4
Medium Priority
?
552 Views
Last Modified: 2012-08-14
Hello experts:
Using SQL2000.  I need to get the due date for a certain date time field.  I have a table where the date & time submitted is entered by the system (i.e. 12/28/2009 4:39:50 PM)  I need to create a due date of 8 hrs from date submitted.  This due date should take weekends, working hours (8am - 5pm) and holidays into consideration.  I've researched this and found I needed to create a calendar table.  I created one that contains work hours, holidays, weekends etc.  I also found this function (F_TABLE_DATE) and was able to run it but it's looking at the # of working dates between two dates.  I have not been able to figure out how to change it to give me the due date of 8 hrs after submission date.

Any help will be greatly appreciated.


function [dbo].[F_TABLE_DATE]
(
	@FIRST_DATE		datetime,
	@LAST_DATE		datetime
)

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

Open in new window

0
Comment
Question by:IEHP1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Expert Comment

by:vinurajr
ID: 26290220
If u have the created date then u can easily create the due date by adding 8 hrs to that..
why are u making your problem complicated..?
0
 
LVL 4

Accepted Solution

by:
Answering4uAnt earned 1000 total points
ID: 26291316
vinu, I think he needs to take into consideration holidays / days off ..etc.. so it is in fact 8 working hours that he needs to add to the original date to get the due date.
0
 

Author Comment

by:IEHP1
ID: 26294252
That's correct I need to add 8 working hours to the date submitted.  Example: if submit date is 01/15/2010 3:00:00 PM due date/time would be 01/19/2010 1:00:00 PM  (16th & 17th are weekend days and 18th is a holiday).  Thanks
0
 
LVL 8

Assisted Solution

by:vinurajr
vinurajr earned 1000 total points
ID: 26300898
This is again more easier u know the daily working hours...
Find the Hours Remaining For the day......
check for the Other Remainig Hours to work in next working day...
Find the Next working day...  and add the Remaining Hours

Kindly Use the Working day Table as only contains the working days...
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question