T-SQL Between Dates Confusion

by

A date is a point in time, not a time span.

'12/31/2010' is a point, too. Namely, it’s the midnight of the 31st of December.
Everything that happened after this point is ignored.
That’s exactly the behaviour you want (even if you haven’t realised that yet).

Do not think that when you choose to omit the time part, it is magically assumed to be "any". It’s going to be "all zeroes", that is, the midnight.

If you want to include the entire day in your query without having to specify 23:59:59 (which, by the way, excludes the last second of the day, between the moment 23:59:59 of the current day and the moment 00:00:00 of the next day), you can do that either by using strict inequalities (>, <) bounded by the first points of time you don’t want:

WHERE TRANDATE >='12/01/2010 00:00:00' and TRANDATE < '01/01/2011'

or by comparing date values casted to DATE:

WHERE CAST(TRANDATE AS DATE) between '12/01/2010' and '12/31/2010'

(it is okay to put this type of cast in a WHERE clause, it is sargable).

More Related Contents:

  1. Check if table exists in SQL Server
  2. How do I generate a random number for each row in a T-SQL select?
  3. How to calculate age in T-SQL with years, months, and days
  4. Levenshtein distance in T-SQL
  5. Convert multiple rows into one with comma as separator [duplicate]
  6. How to get sp_executesql result into a variable?
  7. Select query to remove non-numeric characters
  8. How to convert Seconds to HH:MM:SS using T-SQL
  9. T-SQL: Selecting rows to delete via joins
  10. SQL : BETWEEN vs =
  11. Combining INSERT INTO and WITH/CTE
  12. T-SQL Pivot? Possibility of creating table columns from row values
  13. Getting the minimum of two values in SQL
  14. T-SQL CASE Clause: How to specify WHEN NULL
  15. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  16. t-sql get all dates between 2 dates [duplicate]
  17. How to pivot table with T-SQL?
  18. How can I generate a temporary table filled with dates in SQL Server 2000?
  19. Testing for inequality in T-SQL
  20. Can I use the MERGE statement in SQL Server 2005?
  21. Replace a newline in TSQL
  22. T-SQL – function with default parameters
  23. How to use an Alias in a Calculation for Another Field
  24. Using a cursor with dynamic SQL in a stored procedure
  25. How can I insert binary file data into a binary SQL field using a simple insert statement?
  26. Convert unix epoch timestamp to TSQL datetime
  27. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
  28. How to parse string into date?
  29. Find last sunday
  30. INSERT INTO @TABLE EXEC @query with SQL Server 2000

Leave a Comment