search between to scaler in sql

try this.

SELECT * FROM TABLE WHERE TimeField BETWEEN startTime AND endTime

If you have exact data and time in those startTime and endTime, then go for BETWEEN.
For example -

WHERE myDateTime 
      BETWEEN 
          '2008-01-01 00:00:00'
      AND 
          '2008-03-31 23:59:59.993'

Otherwise you should compare by Greater Than and Less Than operators.
Else you will miss many rows when it will compare it to DateTime Field.

For example -

WHERE myDateTime 
      BETWEEN 
          '2008-01-01'
      AND 
          '2008-03-31'

Here it won't select the date '2008-03-31 09:37', but it falls in the range.
Reason is, it converts the query to below.

WHERE myDateTime 
      BETWEEN 
          '2008-01-01 00:00:00'
      AND 
          '2008-03-31 00:00:00'

So, a much better approach is to abandon the BETWEEN condition, and do this instead:

WHERE myDateTime >= '2008-01-01'
      AND 
      myDateTime  < '2008-04-01'

Reference - Using BETWEEN with DATETIMEs in SQL[^]