CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6743

Unable to set a condition in SQL Server 2008 R2

I have the following data set in the test table:

create table test
(
columndate date,
columntime datetime
)

insert into test values('2014-01-01','22:00:00')
insert into test values('2014-01-02','06:00:00')
insert into test values('2014-01-03','23:00:00')
insert into test values('2014-01-04','05:00:00')
insert into test values('2014-02-01','10:00:00')
insert into test values('2014-02-01','13:00:00')
insert into test values('2014-02-01','15:00:00')
insert into test values('2014-02-01','05:00:00')      

columndate       columntime
------------------------------------
2014-01-01  1900-01-01 22:00:00.000
2014-01-02  1900-01-01 06:00:00.000
2014-01-03  1900-01-01 23:00:00.000
2014-01-04  1900-01-01 05:00:00.000
2014-02-01  1900-01-01 10:00:00.000
2014-02-01  1900-01-01 13:00:00.000
2014-02-01  1900-01-01 15:00:00.000
2014-02-01  1900-01-01 05:00:00.000

Now I want to show only night timing in the result for example:

columndate       columntime
-----------------------------------
2014-01-01  1900-01-01 22:00:00.000
2014-01-02  1900-01-01 06:00:00.000
2014-01-03  1900-01-01 23:00:00.000
2014-01-04  1900-01-01 05:00:00.000
2014-02-01  1900-01-01 05:00:00.000

For which I am trying the following script:

select * from test
where columndate between '2014-01-01' and  '2014-02-01'
  and cast(columntime as time) between '06:00:00' and '23:00:00'

Note: I will not get the record of timing 05:00:00

But when I use the following script:

select * from test
where columndate between '2014-01-01' and  '2014-02-01'
  and cast(columntime as time) between '05:00:00' and '23:00:00'

Note: I will get expected result but I am getting record of timing 06:00:00 also which I don't want to show.

How can I fix it?

Upvotes: 1

Views: 44

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726889

You were really close: instead of BETWEEN you need to use a pair of >= and <=, because you need times outside an interval:

select * from test
where columndate between '2014-01-01' and  '2014-02-01'
  and (cast(columntime as time) <= '05:00:00' OR cast(columntime as time) >= '23:00:00')

Demo.

Upvotes: 2

apomene
apomene

Reputation: 14389

Does this meet your needs??:

select * from test
where columndate between '2014-01-01' and  '2014-02-01'
  and cast(columntime as time) between '05:00:00' and '23:00:00' and columntime ! ='06:00:00'

Upvotes: 0

Related Questions