Where the Error Is in UDF_Function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Where the Error Is in UDF_Function?

Author

Topic

majidbhutta
Starting Member

13 Posts

Posted - 2005-12-01 : 16:35:45

Please Correct This Udf_function.
below is the function that compares only time .there are 3 datetime variables.
@StartTime datetime
@EndTime datetime
@DayTime datetime

@StartTime is always less than @EndTime regardless of its dates.and @DayTime may be any time. We have to calculate whether @DayTime lies between @StartTime and @EndTime or not. And on its behalf we return a value.if @DayTime lies between @StartTime and @EndTime return 1 else return 0.

But tested with these values result is not ok.
set @StartTime='12/1/2005 12:30:49 PM'
set @EndTime='12/2/2005 4:30:49 AM'
set @DayTime='2005-12-02 00:56:02.730'
select dbo.udf_IsShiftValid(@StartTime,@EndTime,@DayTime )

it returns 0 where i think it should return 1. plz correct me where m i wrong.

plz correct this function according to the requirements.

CREATE FUNCTION dbo.udf_IsShiftValid(@StartTime datetime, @EndTime datetime,@DayTime datetime)
RETURNS int AS
BEGIN
DECLARE @RtValue AS int
IF
convert(varchar,@DayTime,114)>=convert(varchar,@StartTime,114) AND convert(varchar,@DayTime,114)<convert(varchar,@EndTime,114)
BEGIN
SET @RtValue=1
END
ELSE
BEGIN
SET @RtValue=0
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-01 : 18:16:07

>>@StartTime is always less than @EndTime regardless of its dates

if you disregard the date portion, doesn't your @StartTime and @endTime values violate the rule?
@startTime = 12:30:49 PM
@endTime = 4:30:49 AM

in this case isn't starttime greater than endtime?

Be One with the Optimizer
TG

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-01 : 19:23:55

majidbhutta

on top of what TG says above, just get rid of the convert statements - you have specified in the function declaration that these must already by datetime so therefore there's no conversion required. Unless your requirements are that these can be varchars.

Secondly, your test data values are varchar and not datetime, and this means you have an implicit conversion occurring which you are neither aware of nor controlling. So your test values should be as follows:

set @StartTime=convert(datetime, '12/1/2005 12:30:49 PM',101)
set @EndTime=(datetime, '12/2/2005 4:30:49 AM',101)
set @DayTime=(datetime, '2005-12-02 00:56:02.730', 121)

CREATE FUNCTION dbo.udf_IsShiftValid(@StartTime datetime, @EndTime datetime,@DayTime datetime) 
RETURNS int AS 
BEGIN 
DECLARE @RtValue AS int
IF
@DayTime>=@StartTime AND @DayTime<@EndTime
BEGIN
SET @RtValue=1
END
ELSE
BEGIN
SET @RtValue=0
END

better still, why not make your function more intelligent so that you don't have to have the start and end times in any particular order?

CREATE FUNCTION dbo.udf_IsShiftValid(@StartTime datetime, @EndTime datetime,@DayTime datetime) 
RETURNS int AS 
BEGIN 

DECLARE @RtValue AS int
declare @st as datetime
declare @et as datetime

//if dates are supplied in wrong order, swap them
if @starttime > @endtime 
begin
    set @st = @endtime
    set @et = @starttime
end
else
begin
    set @et = @endtime
    set @st = @starttime
end

IF @DayTime>=@st AND @DayTime<@et
BEGIN
    SET @RtValue=1
END
ELSE
BEGIN
    SET @RtValue=0
END

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-01 : 21:56:55

This seems simpler, and works OK.


create function dbo.udf_IsShiftValid
(
	@StartTime	datetime,
	@EndTime	datetime,
	@DayTime	datetime
) 
returns int 
as 
begin
if @DayTime between @StartTime and @EndTime   return 1
-- In case the Start and End time are reversed
if @DayTime between @EndTime and @StartTime   return 1
return 0
end
go


print '*** Valid ***'
select 	[Shift Valid] = dbo.udf_IsShiftValid
		(
		'2005-12-01 12:30:49.000',
		'2005-12-02 04:30:49.000',
		'2005-12-02 04:30:49.000'
		)
union all
select 	[Shift Valid] = dbo.udf_IsShiftValid
		(
		'2005-12-01 12:30:49.000',
		'2005-12-02 04:30:49.000',
		'2005-12-01 12:30:49.000'
		)
union all
select 	[Shift Valid] = dbo.udf_IsShiftValid
		(
		-- Start and End reversed
		'2005-12-02 04:30:49.000',
		'2005-12-01 12:30:49.000',
		'2005-12-01 12:30:49.000'
		)
union all
select 	[Shift Valid] = dbo.udf_IsShiftValid
		(
		-- Start and End reversed
		'2005-12-02 04:30:49.000',
		'2005-12-01 12:30:49.000',
		'2005-12-02 04:30:49.000'
		)


print '*** Not Valid ***'
select 	[Shift Valid] = dbo.udf_IsShiftValid
		(
		'2005-12-01 12:30:49.000',
		'2005-12-02 04:30:49.000',
		'2005-12-02 04:30:49.003'
		)
union all
select 	[Shift Valid] = dbo.udf_IsShiftValid
		(
		'2005-12-01 12:30:49.000',
		'2005-12-02 04:30:49.000',
		'2005-12-01 12:30:48.997'
		)
union all
select 	[Shift Valid] = dbo.udf_IsShiftValid
		(
		-- Start and End reversed
		'2005-12-02 04:30:49.000',
		'2005-12-01 12:30:49.000',
		'2005-12-01 12:30:48.997'
		)
union all
select 	[Shift Valid] = dbo.udf_IsShiftValid
		(
		-- Start and End reversed
		'2005-12-02 04:30:49.000',
		'2005-12-01 12:30:49.000',
		'2005-12-02 04:30:49.003'
		)



*** Valid ***
Shift Valid 
----------- 
1
1
1
1


(4 row(s) affected)

*** Not Valid ***
Shift Valid 
----------- 
0
0
0
0

(4 row(s) affected)

CODO ERGO SUM

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-01 : 22:06:56

except that between includes the "equals to" on both ends of the comparison (which I suspect is probably what you want, but your code above had your comparison only "one-ended" ie strictly less than)

but if you're OK about swapping ends, then as the colonel says...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources