1

Topic: Data sampling in a range

Good afternoon!
Prompt as it is possible to correct request.

SELECT TOP 100
[DateTime];
CAST (CASE WHEN DateTime BETWEEN ' 2017-11-12 07:00:00.000 ' AND ' 2017-11-12 19:00:00.000 ' THEN 2 ELSE 1 END as int) as Smen
FROM [CMI1]. [dbo]. [Table2]
where (DateTime BETWEEN ' 2017-11-12 ' AND ' 2017-11-13 ')
order by [DateTime] desc

Result:
DateTime, Smen
2017-11-12 23:53:43.967,1
...,...
2017-11-12 16:34:17.890,2
...,...
2017-11-12 05:58:40.217,1
PS. A dots, set of the data
The given request works only for current date but if to set a range for 2 or more days in CAST restriction will not work from 7 o'clock till 19 o'clock any more. It is necessary that in column Smen registered 2 if date in a range with 7 to 19 and 1 if date out of this range.

2

Re: Data sampling in a range

SELECT TOP 100
[DateTime];
CAST (CASE WHEN cast (DateTime as time) BETWEEN ' 07:00:00.000 ' AND ' 19:00:00.000 ' THEN 2 ELSE 1 END as int) as Smen
FROM [CMI1]. [dbo]. [Table2]
where (DateTime BETWEEN ' 2017-11-12 ' AND ' 2017-11-13 ')
order by [DateTime] desc

3

Re: Data sampling in a range

There was all simply) Thanks big.
... It is necessary to go more to smoke sql a manual)
PS: our server 2005, and it did not support DateTime as time
Replaced so, suddenly at whom the same error will be.

CAST (CASE WHEN CONVERT (varchar (8), DateTime, 108) BETWEEN ' 07:00:00.000 ' AND ' 19:00:00.000 ' THEN 2 ELSE 1 END as int) as Smen

4

Re: Data sampling in a range

Declare @BeginDate DateTime = ' 20170101'
, @EndDate DateTime = ' 20170101'
select top 100
DateTime
, case when DatePart (Hour
, DateAdd (Hour
, -7
, DateTime)) <12
then 1 else 2 end as ChangeOfJob
from table1
where DateTime> = @BeginDate
and DateTime <= @EndDate