-2

Given a specified time value and an interval value:

Specified Time: 13:25:00
Interval Value: 00:20:00

How can I filter the following table of values to return times that are the specified Interval either side of the Specified Time.

12:45:24
13:05:00
13:50:30
14:50:32
15:15:10

I want a function or query to check if '13:25:00' has '00:20:00' difference with any of the times in table.

The output should return:

13:05:00
Tanner
  • 22,205
  • 9
  • 65
  • 83
Amin AmiriDarban
  • 2,031
  • 4
  • 24
  • 32

3 Answers3

1

If we are understanding your question correctly, you want all the times that are bigger than 20 minutes from your given (special) time.

To achieve this, just do a select with a where clause that contains a clause looking like this: abs(datediff(minute, tableDate, @specialdate)) > 20

SQLFiddle sample and code example:

declare @specialDate datetime = '1900-01-01 13:25:00'

select *
  from SampleData
 where abs(datediff(minute, SomeDate, @specialDate)) > 20

Note that I set the dates of the Datetime columns to 1900-01-01 as an obscure reference, adjust according to your settings.

You will need the ABS in the line to make sure that both variants of the resulting datediff are checked (It can either bring back 0, > 0 or < 0)

References:
MSDN: DATEDIFF
MSDN: ABS

Bernd Linde
  • 2,098
  • 2
  • 16
  • 22
1

Based on the information you have provided, I assume you want to get values from the list that are the specified period either side of your "special time".

Here's one way to do it using DATEADD:

-- temp table for your sample data
CREATE TABLE #times ( val TIME )

INSERT  INTO #times
        ( val )
VALUES  ( '12:45:24' ),
        ( '13:05:00' ),
        ( '13:50:30' ),
        ( '14:50:32' ),
        ( '15:15:10' )

DECLARE @special_time TIME = '13:25:00'      
DECLARE @diff_value TIME = '00:20:00'

-- variable will hold the total number of seconds for your interval
DECLARE @diff_in_seconds INT

-- gets the total number of seconds of your interval -> @diff_value 
SELECT  @diff_in_seconds = DATEPART(SECOND, @diff_value) + 60
        * DATEPART(MINUTE, @diff_value) + 3600 * DATEPART(HOUR, @diff_value)

-- get the values that match the criteria
SELECT  *
FROM    #times
WHERE   val = DATEADD(SECOND, @diff_in_seconds, @special_time)
        OR val = DATEADD(SECOND, -( @diff_in_seconds ), @special_time)

DROP TABLE #times

Note that the WHERE clause filters the results by adding and subtracting the difference. The subtraction is achieved by making the @diff_in_seconds negative.

Tanner
  • 22,205
  • 9
  • 65
  • 83
  • Thanks for your answer can you please change it for me to work for this solution 1.my times are inserted in table before, so i want to use select to grab them 2.that special time can be parametric 3.that X time can be parametric – Amin AmiriDarban Apr 20 '15 at 09:19
  • @user1998368 I don't really understand what you mean with your requirements. My answer is simply mocking up a table to replicate your data and then selecting from it, so that sounds like it covers point 1. The special time and X time are both declared as variables / parameters, so that sounds like it covers points 2 & 3. – Tanner Apr 20 '15 at 09:22
0

Here is a solution:

create table t(t time);

insert into t
values
    ('12:45:24'),
    ('13:05:00'),
    ('13:50:30'),
    ('14:50:32'),
    ('15:15:10')

declare @st time = '13:25:00'
declare @dt time = '00:20:00'

select * from t
where abs(datediff(ss, t, @st)) - datediff(ss, '00:00:00', @dt) = 0

abs(datediff(ss, t, @st) will hold difference in seconds between times in table and special time. You compare this difference to difference between 00:00:00 and interval datediff(ss, '00:00:00', @dt)

Output:

t
13:05:00.0000000

Fiddle http://sqlfiddle.com/#!6/05df4/1

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75