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 converting the search criteria date to milliseconds
i.e.
Dec 13 2011 00:00:00 ='1323727200000'
Dec 14 2011 00:00:00 ='1323813600000'`
SELECT *
FROM log_file
WHERE submission_date BETWEEN '1323727200000' AND '1323813600000'
This query will search for Dec 13 Midnight
to Dec 14 Midnight
, but I want to skip the upper limit value i.e. from Dec 13 2011 00:00:00
to Dec 13 2011 59:59:59
. For this I thought to use >= and <
. Is this a right approach?
Having done this, I have a iBatis xml where I am writing the following which is giving me error.
<isNotEmpty prepend="AND" property="submissiondate">
submission_date <![CDATA[ >= ]]> #submissiondate # AND <![CDATA[ < ]]> #submissiondate #
</isNotEmpty>
Please suggest if this is the right approach.
Thanks
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)
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...
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