CODEWITHSUNDEEP
sqlsql-server-2008
user1446930
user1446930

Reputation: 47

SQL Server 2008 find time

I am trying to get the accesstime between two times.

Example

accesstime    starttime   endtime
23:00         22:00       00:00:00

My query look like this

select accesstime 
from myTable 
where accesstime  between '22:00:00'and '00:00:00'

When I run the query I get no results

Why?

Upvotes: 0

Views: 187

Answers (3)

M-Javad
M-Javad

Reputation: 1

i think you can Convert times to datetime and by dateadd function add ONE day to time '00:00:00' and then it works true.

the sample code is here:

CREATE table EX_Time (accesstime nvarchar(100),starttime nvarchar(100),endtime nvarchar(100)) 

INSERT INTO EX_Time(accesstime,starttime,endtime)
VALUES (N'23:00:00',N'22:00:00',N'00:00:00')

select * 
FROM Ex_Time
Where convert(datetime,accesstime) BETWEEN convert(datetime,'22:00:00')
AND dateadd(day,1,convert(datetime,'00:00:00'))

Upvotes: 0

8kb
8kb

Reputation: 11406

Information about BETWEEN:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

In your table, accesstime '23:00' (test expression) is greater than '22:00:00' (begin expression) but is not less than '00:00:00' (end expression) and so it returns false.

In order to see results, change your query to: 

select accesstime from myTable 
where accesstime  between '00:00:00' and '23:00:01'

Upvotes: 1

Gonzalo.-
Gonzalo.-

Reputation: 12682

Because the time goes from 00:00:00 to 23:59:59

so, 22:00 is bigger than 00:00:00 in the same day

edit: This considering as a DateTime. If they are Varchar, you have the same problem, because 22:00 as a Varchar is bigger than 00:00:00

Upvotes: 1

Related Questions