1

I have a table DATATABLE that contains a timeseries that looks something like this:

2018-01-11 09:00:00.000
2018-01-11 09:00:00.000
2018-01-11 09:45:00.000
2018-01-11 09:45:00.000
2018-01-11 10:00:00.000
2018-01-11 10:00:00.000
2018-01-11 10:15:00.000
2018-01-11 10:15:00.000

So if I want to extract the timeseries, I would query:

SELECT * FROM DATATABLE

Now what I want, is to be able to extract part of the timeseries that satisfies specific criteria, which changes each time. The criteria are stored as ranges of values, and so far I have created a procedure that extracts those criteria. One sample criterion would be that the time part of the timeseries should have a value that is contained int he following:

00:00:00
00:30:00
01:00:00
01:30:00
02:00:00
02:30:00
03:00:00
03:30:00
04:00:00
04:30:00
05:00:00
05:30:00
06:00:00
06:30:00
07:00:00
07:30:00
08:00:00
08:30:00
09:00:00
09:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00

In other words, the values 2018-01-11 09:45:00.000 and 2018-01-11 10:15:00.000 should not be part of my final result. The final result would therefore look like:

2018-01-11 09:00:00.000
2018-01-11 09:00:00.000
2018-01-11 10:00:00.000
2018-01-11 10:00:00.000

The criterion that I want to set, in English would be:

''Select all the values from my timeseries that have a time part that is (exactly) contained in the series of times I provide you.''

What I wrote so far is:

SELECT * FROM DATATABLE
WHERE DATEPART(MINUTE,[DATATABLE].[ISSUETIMES])=(SELECT IssueTimes FROM @IssueTimes)

where @IssueTimes is a temporary table variable where I store the range of values I want to use as criteria and [DATATABLE].[ISSUETIMES] is the column that contains the timeseries that I want to filter.

The error I get is the following:

Operand type clash: time is incompatible with int

So i get that this probably means that am trying to compare times with int, but i do not know how to proceed as I am kind of new to SQL.

I hope my formulation was good enough to be understandable :)

  • You should not use datediff that returns you int, but simply cast your datetime as TIME, and work with it – sepupic Jan 16 '18 at 10:49
  • I also tried this: SELECT * FROM DATATABLE WHERE CAST(DATATABLE.ISSUETIMES AS TIME(0)) = (SELECT IssueTimes FROM @IssueTimes) and i get the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – Nisfa Jan 16 '18 at 10:53
2

That is what I suggested in comment:

declare @dt table (dt datetime);
insert into @dt values
('2018-01-11 09:00:00.000'),
('2018-01-11 09:00:00.000'),
('2018-01-11 09:45:00.000'),
('2018-01-11 09:45:00.000'),
('2018-01-11 10:00:00.000'),
('2018-01-11 10:00:00.000'),
('2018-01-11 10:15:00.000'),
('2018-01-11 10:15:00.000');

declare @tm table (tm time);

insert into @tm values
('00:00:00'),
('00:30:00'),
('01:00:00'),
('01:30:00'),
('02:00:00'),
('02:30:00'),
('03:00:00'),
('03:30:00'),
('04:00:00'),
('04:30:00'),
('05:00:00'),
('05:30:00'),
('06:00:00'),
('06:30:00'),
('07:00:00'),
('07:30:00'),
('08:00:00'),
('08:30:00'),
('09:00:00'),
('09:30:00'),
('10:00:00'),
('10:30:00'),
('11:00:00'),
('11:30:00'),
('12:00:00'),
('12:30:00');

select *
from @dt
where cast(dt as time) in (select tm from @tm);

Alternatively, use exists:

select *
from @dt t
where exists (select * 
              from @tm t1
              where t1.tm = cast(t.dt as time));
  • Tried it and it works and I just up voted your answer. Thanks :) – Nisfa Jan 16 '18 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.