To determine working hour using T-SQL

create function [dbo].[fn_IsWorkingHour]
(
@Datetime datetime
)
RETURNS bit
AS
BEGIN
declare @result bit
declare @currTime datetime
set @currTime = (@Datetime – CAST(ROUND(CAST(@Datetime AS float), 0, 1) AS datetime))
set @result = 0
if datediff(minute,’1900-01-01′,@currTime) >= 540 and datediff(minute,’1900-01-01′,@currTime) <= 1050
  set @result = 1
else
  set @result = 0
return @result
END

Note: Highlighted in red (working hour 9 AM to 5.30 PM)
540 = 9:00 AM  = (9 * 60)
1050 = 5:30 PM  = (17.5 * 60)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s