asked on

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!

plusone3055

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)

PortletPaul

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.

Mark Wills

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

Mark Wills

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

Valliappan AN

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.

Mark Wills

@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 ?

Valliappan AN

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?

Mark Wills

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)

PortletPaul

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)

ASKER CERTIFIED SOLUTION

Valliappan AN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial