Can we retrieve data between two dates, including the two dates? For example, if I require data from 01/01/2008 to 31/03/2008, using "between" only gives the data from 02/01/2008 to 30/03/2008. Is there any way we can get the data including the dates 01/01/2008 and also 31/03/2008?

    Requires Free Membership to View

You may not have given enough information for me to diagnose your problem correctly. BETWEEN does include the endpoints of the range. This is the default behaviour.

What might be happening is that you actually have datetime values in your table, rather than date values. For example, let's say the values in your table are:

myDateTime
2008-01-01 01:01
2008-01-22 01:22
2008-02-01 02:01
2008-02-29 02:29
2008-03-30 03:30
2008-03-31 09:37

Now run a query with this condition:

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

You will see that the last value, 2008-03-31 09:37, is missing. This is because it is outside the range specified by the BETWEEN condition. The reason is due to fact that the values in the BETWEEN condition are converted to datetime values in order to do the comparisons, so the condition is actually:

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

Thus, all the datetime values for that last day will be missing (except for those, if any, which specify midnight exactly).

When dealing with datetimes like this, you could try to specify the upper end of the range explicitly, like this:

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

However, the problem with this is that you have to be sure you specify the actual last possible datetime value for that date, and this becomes quite tricky as it depends on which version of which DBMS you're using.

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

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

Notice that the operator in the second condition is less than, not less than or equal.

The bonus here is that you don't have to waste any time calculating the last day of the month (28th, 29th, 30th, or 31st). Just make it less than the 1st of the following month. Easier to code, just as efficient, and guaranteed to get all included datetime values no matter what precision the database uses (microsecond, 3 microseconds, whatever).

Neat, eh?

This was first published in July 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: