compare two times - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

compare two times

Author

Topic

bekeer020
Starting Member

24 Posts

Posted - 2012-06-24 : 06:41:04

Dear all

I have table with columns nvarchatr "Start_TIme,End_TIme"

I want to create function to compare if cuurent time between Start_Time and End_Time return "Yes" for example

how can i do like this

this is my example but not working

=========================
ALTER function [dbo].[check_class_now]
(
@Start_Time nvarchar(50),
@End_Time nvarchar(50)
)
returns nvarchar(50)
as
begin
declare @dStart_Time datetime
declare @dEnd_Time datetime
declare @Result nvarchar(50)

set @dStart_Time=(select cast(@Start_Time as datetime))
set @dEnd_Time=(select cast(@End_Time as datetime))

if (dbo.gettimenow()>=@dStart_Time) and (dbo.gettimenow()<=@dEnd_Time)

set @Result='yes'

return @Result
END
=============================

ALTER function [dbo].[gettimenow]
(
)
returns nvarchar(20)
as
begin

Declare @result nvarchar(20)
set @result= (SELECT SUBSTRING(CONVERT(varchar, GETDATE(), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, GETDATE(), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar,
GETDATE(), 100), 18, 2) AS MyTime)
return @result

end

=====

Note : mt time formate 12 hourse like (1:44 PM )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-24 : 13:09:59

make function like


ALTER function [dbo].[check_class_now]
(
@Start_Time datetime,
@End_Time datetime
)
returns bit
as 
begin 

declare @Result bit

set @Result=CASE WHEN DATEADD(dd,-1 * DATEDIFF(dd,0,GETDATE()),GETDATE()) BETWEEN CONVERT(datetime,@Start_Time) AND CONVERT(datetime,@End_Time) 
                 THEN 1 
                 ELSE 0 
            END

return @Result
END


then call it like 

SELECT dbo.[check_class_now] (starttime,endtime),...
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bekeer020
Starting Member

24 Posts

Posted - 2012-06-24 : 18:27:53

Done

thank you visakh16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources