December 1, 2005 at 3:28 pm
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 modify this function so as it returns 1 if the @DayTime Lies Between @StartTime And @EndTime else one( BUt keep in Mind i m required to compare only time not the dates and @startTime value is always less than @EndTime value)
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,1
14) AND
convert(varchar,@DayTime,114)<convert(varchar,@EndTime,114)
BEGIN
SET @RtValue=1
END
ELSE
BEGIN
SET @RtValue=0
END
RETURN @RtValue
END
December 2, 2005 at 2:03 am
Try:
IF OBJECT_ID('dbo.udf_IsShiftValid') IS NOT NULL
DROP FUNCTION dbo.udf_IsShiftValid
GO
CREATE FUNCTION dbo.udf_IsShiftValid
(@StartTime datetime
, @EndTime datetime
, @DayTime datetime)
RETURNS int
AS
BEGIN
DECLARE @RtValue AS int
SET @RtValue=0 -- assume failure
IF @DayTime BETWEEN @StartTime AND @EndTime
SET @RtValue=1
RETURN @RtValue
END
GO
DECLARE @StartTime datetime, @EndTime datetime, @DayTime datetime
-- Easy to do with the time only
set @StartTime='2005-12-01 12:30:49'
set @EndTime='2005-12-02 16:30:49'
set @DayTime='2005-12-02 00:56:02'
select dbo.udf_IsShiftValid(@StartTime,@EndTime,@DayTime)
Andy
December 2, 2005 at 6:12 am
-- or (not for millseconds)
if object_id('dbo.udf_IsShiftValid') is null
execute('create function dbo.udf_IsShiftValid() returns integer as begin return 12 end')
go
alter function dbo.udf_IsShiftValid
(@StartTime datetime
, @EndTime datetime
, @DayTime datetime)
RETURNS integer
AS
BEGIN
DECLARE @RtValue AS int
-- compare times regardless of its dates
select @StartTime = cast('20051202 ' + convert(varchar(8), @StartTime, 114) as datetime)
select @DayTime = cast('20051202 ' + convert(varchar(8), @DayTime, 114) as datetime)
select @EndTime = cast('20051202 ' + convert(varchar(8), @EndTime, 114) as datetime)
select @RtValue = case when @DayTime BETWEEN @StartTime AND @EndTime then 1 else 0 end
return(@RtValue)
END
GO
DECLARE @StartTime datetime, @EndTime datetime, @DayTime datetime
-- Easy to do with the time only
set @StartTime='2005-12-01 12:30:49'
set @EndTime='2005-12-02 16:30:49'
set @DayTime='2009-12-02 14:56:02'
select dbo.udf_IsShiftValid(@StartTime,@EndTime,@DayTime)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply