Datediff on Insert - SQL Server Forums

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)

Datediff on Insert

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-28 : 09:50:44

Is it possible to do a query in an insert. I have a table with vacation request where they put in the start date and end date for their vacation.

I need to calculate how many days this is without weekends. I have a separate table with all the weekends and holidays in it that are excluded since they don't have to request these.

My insert statement is currently like this:

INSERT INTO request(emp_id, request_submit_date,
 request_start_date, request_end_date, request_duration, 
request_notes, time_off_id) VALUES (@emp_id, GETDATE(), 
@request_start_date, @request_end_date, @request_duration, 
@request_notes, @time_off_id)

I need to say something like this

INSERT INTO request(emp_id, request_submit_date, 
request_start_date, request_end_date, request_duration,
 request_notes, time_off_id) VALUES (@emp_id, GETDATE(),
 @request_start_date, @request_end_date, 
datediff(request_start_date, request_end_date
 - the exceptions in the weekend holiday table, 
@request_notes, @time_off_id)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 10:01:21

Something like this:

INSERT INTO request(emp_id, request_submit_date, 
request_start_date, request_end_date, request_duration,
 request_notes, time_off_id) 
Select @emp_id, GETDATE(),@request_start_date, @request_end_date, 
(@Request_Start_Date - @Request_End_Date - (select count(*) from Holidays where holidayDate between @Request_Start_Date and @Request_End_Date)) as Request_Duration, @request_notes, @time_off_id

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 10:06:24

I don't think date subtractions are possible in SQL Server.

INSERT		request
		(
			emp_id,
			request_submit_date, 
			request_start_date,
			request_end_date,
			request_duration,
			request_notes,
			time_off_id
		)
Select		@emp_id,
		GETDATE(),
		@request_start_date,
		@request_end_date, 
		1 + DATEDIFF(day, @Request_Start_Date, @Request_End_Date) - (select count(*) from Holidays where holidayDate between @Request_Start_Date and @Request_End_Date),
		@request_notes,
		@time_off_id

Peter Larsson
Helsingborg, Sweden

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-28 : 10:13:03

Kewl, thank you for the prompt response. Okay, I tried it like this:

INSERT INTO request(emp_id, request_submit_date, 
request_start_date, request_end_date, request_duration,
 request_notes, time_off_id) 
Select @emp_id, GETDATE(),@request_start_date, @request_end_date, 
(@Request_Start_Date - @Request_End_Date - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @Request_Start_Date and @Request_End_Date)) as Request_Duration, @request_notes, @time_off_id

When I try to save my Stored Procedure I get the following error:

quote:
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-28 : 10:21:12

Peter, I tried your suggestion, but I get Null as a result after the insert.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-28 : 10:23:22

This is what I have now, but am getting NULL as the result on the request_duration on insert

INSERT		request
		(
			emp_id,
			request_submit_date, 
			request_start_date,
			request_end_date,
			request_duration,
			request_notes,
			time_off_id
		)
Select		@emp_id,
		GETDATE(),
		@request_start_date,
		@request_end_date, 
		1 + DATEDIFF(day, @Request_Start_Date, @Request_End_Date) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @Request_Start_Date and @Request_End_Date),
		@request_notes,
		@time_off_id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 10:29:54

Execute only the SUBQUERY with
select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @Request_Start_Date and @Request_End_Date

to see what that gives. I am not sure what DayOfWeekDate is and does.

1) Are you sure @Request_Start_Date is earlier than @Request_End_Date ?
2) Are you sure none of the parameters in 1) is NULL ?

Peter Larsson
Helsingborg, Sweden

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-28 : 10:33:32

DaysOfWeekDate is just a date time stamp of a day in my holiday table

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-28 : 10:38:51

[code]select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @Request_Start_Date and @Request_End_Date[/code]

When I try to execute this I get no results.

[code]select count(*) from WeekEndsAndHolidays where DayOfWeekDate between 09/15/2006 and 09/22/2006[/code]

Is it possible because the DayOfWeekDate shows like this:

9/17/2006 12:00:00 AM

and the query shows like this

09/22/2006

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 10:39:05

[code]
SELECT @Request_Start_Date = '20061001',--October 1st 2006
@Request_End_Date = '20061115'--November 15th 2006
[/code]
1) select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @Request_Start_Date and @Request_End_Date
2) select 1 + DATEDIFF(day, @Request_Start_Date, @Request_End_Date) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @Request_Start_Date and @Request_End_Date)

If you execute the two queries above, which two output do you get?

Peter Larsson
Helsingborg, Sweden

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-28 : 10:51:35

If I do it like this I get the right result:

select count(*) from WeekEndsAndHolidays where DayOfWeekDate between '09/01/2006' and '09/22/2006'

If I do it without the quotes I get zero.

I am new to SQL so when you say execute the queries, I am right
clicking on the database name in Microsoft SQL Management Studio
and picking new query and then pasting the code in.
Is that what you mean

P.S.

Thank for your help on this!!!!!!!!!!

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-28 : 11:18:25

Any thoughts on what I can change to get a result other than NULL. With your help I am close, but still getting NULL.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-28 : 13:39:22

Okay, nevermind... Your solution works fine. I put it right into the page and it worked. Must be something wrong on my stored procedure.

Thank you so much for your help!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources