SQL “between” not inclusive

by

It is inclusive. You are comparing datetimes to dates. The second date is interpreted as midnight when the day starts.

One way to fix this is:

SELECT *
FROM Cases
WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01'

Another way to fix it is with explicit binary comparisons

SELECT *
FROM Cases
WHERE created_at >= '2013-05-01' AND created_at < '2013-05-02'

Aaron Bertrand has a long blog entry on dates (here), where he discusses this and other date issues.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. T-SQL split string
  3. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  4. How can I get column names from a table in SQL Server?
  5. Conditional WHERE clause in SQL Server
  6. How can I select the first day of a month in SQL?
  7. Split function equivalent in T-SQL?
  8. Is the NOLOCK (Sql Server hint) bad practice?
  9. Multi-statement Table Valued Function vs Inline Table Valued Function
  10. Select statement to find duplicates on certain fields
  11. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  12. Where value in column containing comma delimited values
  13. Parse comma-separated string to make IN List of strings in the Where clause
  14. SQL query to split column data into rows
  15. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  16. Compare DATETIME and DATE ignoring time portion
  17. Split function in SQL Server 2008
  18. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  19. Can I set ignore_dup_key on for a primary key?
  20. Using PIVOT in SQL Server 2008
  21. How do I prevent a database trigger from recursing?
  22. How can I group by date time column without taking time into consideration
  23. TSQL – How to use GO inside of a BEGIN .. END block?
  24. How to combine results of two queries into a single dataset
  25. When using GETDATE() in many places, is it better to use a variable?
  26. Executing SQL Server Agent Job from a stored procedure and returning job result
  27. SQL Server SELECT INTO @variable?
  28. Using CASE Statement inside IN Clause
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment