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, if you have to skip the upper limit - you should use
Of course - if your column's type is DATETIME
In your case, where "date" seems to be of type
BIGINT
, why not just subtract 1 from the upper interval limit?Of course, this wouldn't work with floating point numbers or decimals...
Yes, you'd use
>=
and<
typically for time/date range queriesAlternatively, 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
)Note: subtracting 1 would probably be OK if everything is milliseconds...
Edit, example of why 3ms
And interestingly, are you sure this is midnight?
For 1323813600 seconds, I get
2011-12-13 22:00:00
On SQL Server:
On MySQL