Ladies / Gentlemen


I would like to find out if this is a valid use of the 'Between' Statement


Select *
From Bids
Where GetDate() is Between StartDate and EndDate


Mark Moss

Absolutely. I've used a similar expression before. To demonstrate:

create table test4 (col1 datetime, col2 datetime)
GO

insert into test4 values (dateadd(hh, -1, getdate()), dateadd(hh, 1,
getdate()))

select * from test4 where getdate() between col1 and col2

Show quoteHide quote

Mark,

I think it is valid--but then, why wouldn't it be?

Assuming you have the [pubs] database on your SQL box, you can successfully
run this in SQL Query Analyzer, although I doubt there will be any
qualifying rows:

use pubs
go
select *
from dbo.sales s
where (getdate() between s.ord_date and s.ord_date)


Robert



Show quoteHide quote

Technically, yes, perfectly valid.  However, symantically correct is the
real question.  If your values are defined like:

2001-01-01  00:00:00    2001-01-31 00:00:00
2001-01-31  00:00:00    2001-02-28 00:00:00

Where there exists a StartDate that equals an EndDate, then you will get
overlaps.  If you defined your endpoints as:

2001-01-01  00:00:00    2001-01-31 23:59:59.997
2001-02-01 00:00:00    2001-02-28 23:59:59.997

Assuming they are datetime values, then yes, you are good to go.

Show quoteHide quote

Clean it up a bit: drop the "is", replace SELECT * in producicton code
and use the Standard CURRENT_TIMESTAMP now that you have it:

SELECT <<list of columns>>
  FROM Bids
WHERE CURRENT_TIMESTAMP BETWEEN start_date AND end_date;

You just have to watch out for the time part of a DATETIME data type.
You probably want the start_date to be set to 00:00:00.00 and the
end_date to have "23:59:59.99" so you get the full days.

I have a section in my datetime article that addresses range queries and why BETWEEN should be
avoided for datetime range searches: http://www.karaszi.com/SQLServer/info_datetime.asp

Show quoteHide quote

how to obtain further error information?
difficulty with SELECT SCOPE_IDENTITY();
Parsing Numeric from Varchar
General SQL Error
Saving HTML into SQL2000
Find specific text in a string
Deleting primary key when no foreign records exist?
big task?
Old dates
Another T-SQL puzzle