How Can I Exclude Upper Limit In BETWEEN Sql Server

I am using SQL Server as my database. I am searching for a row for the date that I have entered. This means searching rows where submission_date is exactly '12/13/2011'. First I am

Solution 1:

Yes, you'd use >= and < typically for time/date range queries

Alternatively, you could subtract 3 milliseconds from the upper limit to get the highest datetime (not newer datetime2) value for that day (xxx 23:59.59.997)

SELECT * FROM   log_file
WHERE  submission_date BETWEEN 1323714600000 AND 1323801000000-3

Note: subtracting 1 would probably be OK if everything is milliseconds...

Edit, example of why 3ms

SELECT 
    DATEADD(millisecond, -1, '20111214'), -- 2011-12-14 00:00:00.000
    DATEADD(millisecond, -2, '20111214'), -- 2011-12-13 23:59:59.997
    DATEADD(millisecond, -3, '20111214')  -- 2011-12-13 23:59:59.997

And interestingly, are you sure this is midnight?
For 1323813600 seconds, I get 2011-12-13 22:00:00

On SQL Server:

SELECT DATEADD(second, 1323813600, '19700101') 

On MySQL

SELECT FROM_UNIXTIME(1323813600)

Solution 2:

In your case, where "date" seems to be of type BIGINT, why not just subtract 1 from the upper interval limit?

SELECT * FROM log_file 
WHERE submission_date BETWEEN 1323714600000 AND 1323801000000 - 1

Of course, this wouldn't work with floating point numbers or decimals...

Solution 3:

Yes, if you have to skip the upper limit - you should use

WHERE Date >= '20111213' AND Date < '20111214'

Of course - if your column's type is DATETIME