Help Me plz abt this dateTime 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. 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)>= convertvarchar,@StartTime,114) AND convert(varchar,@DayTime,114)< (convert(varchar,@EndTime,114) BEGIN SET @RtValue=1 END ELSE BEGIN SET @RtValue=0 END RETURN @RtValue END -- modified at 21:40 Thursday 1st December, 2005
-
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)>= convertvarchar,@StartTime,114) AND convert(varchar,@DayTime,114)< (convert(varchar,@EndTime,114) BEGIN SET @RtValue=1 END ELSE BEGIN SET @RtValue=0 END RETURN @RtValue END -- modified at 21:40 Thursday 1st December, 2005
Eg :
CREATE FUNCTION dbo.udf_IsShiftValid(@StartTime datetime, @EndTime datetime,@DayTime datetime) RETURNS INT AS BEGIN DECLARE @RET AS INT IF (convert(varchar,@EndTime,114) < convert(varchar,@DayTime,114) ) AND (convert(varchar,@DayTime,114) > convert(varchar,@StartTime,114) ) SET @RET=1 ELSE SET @RET=0 RETURN @RET ENDPlease have a look at this URL also. http://www.databasejournal.com/features/mssql/article.php/3348181[^] -- modified at 12:47 Friday 2nd December, 2005