problem at writting functions in sql server 2005

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

Transact-SQL (2000)

problem at writting functions in sql server 2005

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2006-09-29 : 06:08:39

hi,
here i am trying to calculate the no. of days b/w two dates.i had written two functions in sqlserver2005 as:
first function:
CREATE FUNCTION dbo.GetWorkingDays2
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
IF (DATEDIFF(dd, @StartDate, @EndDate) < 2)
BEGIN
RETURN ( 0 )
END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
@FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 5
WHEN 'Monday' THEN 4
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 2
WHEN 'Thursday' THEN 1
WHEN 'Friday' THEN 0
WHEN 'Saturday' THEN 0
END
IF (@TotalDays < @FirstPart)
BEGIN
SELECT @WorkDays = @TotalDays
END
ELSE
BEGIN
SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
SELECT @LastPart = (@TotalDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END
RETURN ( @WorkDays )
END
GO

but this function is showing incorrect out put. i cant understand what the problem in it.

next i had written another function:
fumction2:
create function dbo.GetDays
(@startdate datetime,
@enddate datetime)
returns int
as
begin
declare @days int
if(DATEDIFF(dd, @startdate, @enddate)!= 0)
@days = datediff(d,@startdate,@enddate)
return(@days)
end
go

it is throwing an error at @days = datediff(d,@startdate,@enddate)
error:Incorrect syntax near '@days'.

really i cant understand why this error is coming.

so any one pls help me on this, and if u have better code than this to calculate no.of days b/w twodays .pls send to me.

thanx in advance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-29 : 06:29:13

Use SET or SELECT to assign value to variable:

SET @days = datediff(d,@startdate,@enddate)

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 06:41:25

Make use the the function F_TABLE_DATE found here at SQLTeam.
It has everything you want and then some.

Peter Larsson
Helsingborg, Sweden

rammohan
Posting Yak Master

212 Posts

Posted - 2006-09-29 : 07:04:33

thank you boss,
its working now
thanx again

rammohan
Posting Yak Master

212 Posts

Posted - 2006-09-29 : 07:05:59

thank you boss,
its working now
thanx again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources