SQL to Determine if time is between two values

Hello Experts,

I need some Sql Server 2008 R2 help.  I have a table structured as:
USE [FrameSchedulerPOC]
GO

/****** Object:  Table [dbo].[ProcessExclusion]    Script Date: 10/08/2013 08:28:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProcessExclusion](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProcessID] [int] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[StartTime] [time] NOT NULL,
	[EndTime] [time] NOT NULL,
 CONSTRAINT [PK_ProcessExclusion] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I need a stored procedure or function that accepts a datetime and can tell if the time falls within the start/stop window.  The problem occurs when we cross midnight.  The start date might be 01012013 and the end date might be 01022020 with starttime of 2200 and endtime of 0300 because we want some process to pause daily during this window for the next few years.  Please help!
TronGodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
you will have to write an app to prompt the user
SQL server does not have an interface to promot the user to enter data.
you can accompish this the quickest (in my opinion)  in VB.NET or C#

Basically write a quick form with a connection to the DB
a simple enter Time and a button

the textbox will be what hte user enters
click the button which will fireoff the stored preocedure

inside the strored procedure

 Make a simple if stateement
(this is Pseudo code)

IF  time <  or >  X

RUN NOW

ELSE

PAUSE  (for however long)
0
PortletPaulfreelancerCommented:
Topics: Office & Productivity Software ,Industry Specific Software ,Developer Tools

I'm very curious about the topics of this question and will add MS SQL Server - but unless you can think of any reason for the existing topics I'd suggest removing all three.


& Just an observation while here:

I'm not  big fan of separating time from date - despite the fact one is able to with date and time data types - but here you have 2 datetime fields.

Will  [StartDate] contain both date and time?
(ie. the time portion of [StartDate] would be equal to [StartTime] )

You might avoid a lot of calculations by just using the datetime fields.
0
Mark WillsTopic AdvisorCommented:
Noticed you have DATETIME in your table for start and end dates. That can contain both the date and the time.

But you also have separate times in that same table.

It is going to be a whole lot easier combining by using the DATE functions.

You can make it either a function or a procedure. Guess some of that decision will depend on what you really want to return to the caller.

Now, time() can go down to the 100 nanosecond, but your example only shows hours and minutes. So, there might be additional work based on precision needed. We can get to millisecond accuracy without any problems. nanosecond accuracy is more of a challenge.

As an example...

declare @sdt   datetime
set @sdt = '20130201 02:02:02'    -- this will become the parameter 

declare @edt   datetime
set @edt = dateadd(day,datediff(day,0,@sdt),0)

;with dt_cte as
(select convert(datetime, dateadd(millisecond, datediff(millisecond,0,[StartTime]),[StartDate])) as start_date_time
       ,convert(datetime, dateadd(millisecond, datediff(millisecond,0,[EndTime]),[EndDate])) as end_date_time
 from ProcessExclusion
 where @sdt >= startdate  -- we do this "where" to limit the range
 and   @edt < enddate)
select count(*) as IN_Process_Exclusion   
from dt_cte
where  @sdt between start_date_time and end_date_time

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mark WillsTopic AdvisorCommented:
The next question is how do you want to use it ?

Do you want to select from it ? Do you want to execute ? Depending on data volumes we could even create a view.

The following are a couple of examples (and could probably do more depending on what you really want).

-- as a view 

create view vw_ProcessExclusion as
with dt_cte as
(select convert(datetime, dateadd(millisecond, datediff(millisecond,0,[StartTime]),[StartDate])) as start_date_time
       ,convert(datetime, dateadd(millisecond, datediff(millisecond,0,[EndTime]),[EndDate])) as end_date_time
 from ProcessExclusion
)
select start_date_time, end_date_time
from dt_cte
go

-- now select from the view

select * 
from vw_ProcessExclusion
where  '20130103 23:23' between start_date_time and end_date_time
go

-- as a scaler function
 
create function udf_ProcessExclusion (@dt datetime)
returns int
as
begin
  declare @sdt   datetime
  set @sdt = @dt

  declare @edt   datetime
  set @edt = dateadd(day,datediff(day,0,@sdt),0)

  declare @IN_Process_Exclusion int

  ;with dt_cte as
  (select convert(datetime, dateadd(millisecond, datediff(millisecond,0,[StartTime]),[StartDate])) as start_date_time
         ,convert(datetime, dateadd(millisecond, datediff(millisecond,0,[EndTime]),[EndDate])) as end_date_time
   from ProcessExclusion
   where @sdt >= startdate
   and   @edt < enddate)
  select @IN_Process_Exclusion = count(*)
  from dt_cte
  where  @sdt between start_date_time and end_date_time
  return @IN_Process_Exclusion
end
go

-- now select from the function

select dbo.udf_ProcessExclusion ('20130103 23:23:23') as am_i_in_or_what
go

-- as a stored procedure

create procedure usp_ProcessExclusion (@dt datetime)
as
begin
  declare @sdt   datetime
  set @sdt = @dt

  declare @edt   datetime
  set @edt = dateadd(day,datediff(day,0,@sdt),0)

  declare @IN_Process_Exclusion int

  ;with dt_cte as
  (select convert(datetime, dateadd(millisecond, datediff(millisecond,0,[StartTime]),[StartDate])) as start_date_time
         ,convert(datetime, dateadd(millisecond, datediff(millisecond,0,[EndTime]),[EndDate])) as end_date_time
   from ProcessExclusion
   where @sdt >= startdate
   and   @edt < enddate)
  select count(*) as IN_Process_Exclusion
  from dt_cte
  where  @sdt between start_date_time and end_date_time
end
go

-- now execute the stored procedure

exec dbo.usp_ProcessExclusion '20130103 23:23:23'
go

Open in new window

0
Valliappan ANSenior Tech ConsultantCommented:
Hi TronGod,

I believe you would like to search the entire table if the given datetime matches the start-stop dates.

You may try if this works:

CREATE PROCEDURE getProcessExclusions
(
  @currDate DateTime
)

AS

BEGIN

SELECT * 
FROM [ProcessExclusion] 
WHERE  @currDate BETWEEN (StartDate+ StartTime) AND (EndDate+ EndTime) 

END

GO

Open in new window


Hope this helps.
0
Mark WillsTopic AdvisorCommented:
@valli_an

What SQL Server are you using ???

Try this :

declare @startdate datetime = '2013-10-12'
declare @starttime time = '12:13:13'

select @startdate + @starttime

Open in new window

And guess what ... IT DOES NOT WORK
Msg 402, Level 16, State 1, Line 4
The data types datetime and time are incompatible in the add operator.

Open in new window


Maybe you were thinking of a different server ?
0
Valliappan ANSenior Tech ConsultantCommented:
To your query, the output i get is: 2013-10-12 12:13:13.000

When i do 'Select @@version' i get this:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Which version do you use?
0
Mark WillsTopic AdvisorCommented:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
0
PortletPaulfreelancerCommented:
well I'll be a monkey's uncle...

it does work in sql 2008, but not 2012

I have never attempted  ( date + time )  before now assuming it didn't work at all.

(but not good if upgrading anyway)

I would strongly recommend datediff/dateadd instead, these will be far more reliable

e.g.
declare @startdate datetime = '2013-10-12'
declare @starttime time = '12:13:13'

select   dateadd(second,datediff(second,'00:00:00',@starttime),@startdate)
0
Valliappan ANSenior Tech ConsultantCommented:
When tested on SQL 2012:

Case1:
Another thing happened, that in [master] DB it does not work, but in other DBs it worked. (add datetime to time directly)

Case2:
In SQL Server 2012, this works :

declare @startdate datetime = '2013-10-12'
declare @starttime time = '12:13:13'

select @startdate + cast(@starttime as datetime)

Open in new window

Also refer:
http://www.sqlservercentral.com/Forums/Topic1279094-3077-1.aspx#bm1279166

Please check if this helps (sql 2008 / sql 2012) :

CREATE PROCEDURE getProcessExclusions
(
  @currDate DateTime
)

AS

BEGIN

SELECT * 
FROM [ProcessExclusion] 
WHERE  @currDate BETWEEN (StartDate+ CAST(StartTime AS datetime)) AND (EndDate+ CAST(EndTime AS datetime)) 

END

GO

Open in new window


Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.