Is This Possible in Sql?
-
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.
Actually i have to use this function on CheckInShfit event of the Employee. @StartTime and @EndTime are the time of the shifts that have already been defined in the database very ago. Now whenever Employee ChecksIn we have to confirm wheather he is entering Between his shift timing that have been Defined already in the database, if his CheckInTime (only time not date) @DayTime lies between his @StartTime and @EndTime then he is allowed to enter (i.e 1) else Not(0).
Now keeing in view the baove Scenario, plz guide me
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,114) AND convert(varchar,@DayTime,114))<convert(varchar,@EndTime,114)
SET @RtValue=1
END
ELSE
BEGIN
SET @RtValue=0
ENDRETURN @RtValue
END -
"...Mind i m required to compare only time not the dates and @startTime value is always less than @EndTime value)..."
Given this statement, the data you supplied is incorrect
set @StartTime='12/1/2005 12:30:49 PM'
set @EndTime='12/2/2005 4:30:49 AM'
Ignoring the dates, 4:30am in the morning is before 12:30 in the afternoon.
--------------------
Colt 45 - the original point and click interface
-
Convert the @StartTime, @EndTime, @DayTime to ticks and then test if the @DayTime >= @StartTime And <= @EndTime.
If you store shift hours convert them as they apply to that day.
Set @S = datediff(ss, '1/1/1970 12:00:00.000 AM', @StartTime)
Set @e = datediff(ss, '1/1/1970 12:00:00.000 AM', @EndTime)
Set @d = datediff(ss, '1/1/1970 12:00:00.000 AM', @DayTime)
If @d >= @S And @DayTime <= @e
Set @retVal = 1
Else
Set @retVal = 0
Return @retVal
-
Is there some reason for making the solution to such a simple problem so complicated ?
You can use the CASE statement or a two line UDF.
alter FUNCTION dbo.udf_IsShiftValid
(@StartTime datetime
, @EndTime datetime
,@DayTime datetime)
RETURNS int AS
BEGIN
IF @DayTime between @StartTime and @EndTime RETURN (1)
RETURN 0
END
go
declare @StartTime datetime
, @EndTime datetime
, @DayTime datetime
set @StartTime = '2005-12-01 12:30:49'
set @EndTime = '2005-12-02 04:30:49'
set @DayTime = '2005-12-02 00:56:02'
select CASE WHEN @DayTime between @StartTime and @EndTime then 1 else 0 end as ShiftStatus_CASE
, dbo.udf_IsShiftValid( @StartTime , @EndTime, @DayTime) as ShiftStatus_UDF
set @StartTime = '2005-12-01 12:30:49'
set @EndTime = '2005-12-02 04:30:49'
set @DayTime = '2005-12-04 00:56:02'
select CASE WHEN @DayTime between @StartTime and @EndTime then 1 else 0 end as ShiftStatus_CASE
, dbo.udf_IsShiftValid( @StartTime , @EndTime, @DayTime) as ShiftStatus_UDF
SQL = Scarcely Qualifies as a Language
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply