Get row where datetime column = today

by

On SQL Server 2008, you would have a new DATE data type, which you could use to achieve this:

SELECT (list of fields)
FROM dbo.YourTable
WHERE dateValue BETWEEN 
   CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE))

The CAST(GETDATE() AS DATE) casts the current date and time to a date-only value, e.g. return ‘2010-04-06’ for April 6, 2010. Adding one day to that basically selects all datetime values of today.

In SQL Server 2005, there’s no easy way to do this – the most elegant solution I found here is using numeric manipulation of the DATETIME to achieve the same result:

SELECT (list of fields)
FROM dbo.YourTable
WHERE dateValue BETWEEN 
   CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AND 
   DATEADD(DAY, 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))

More Related Contents:

  1. How do I fix the error ‘Named Pipes Provider, error 40 – Could not open a connection to’ SQL Server’?
  2. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  3. Parameter Sniffing (or Spoofing) in SQL Server
  4. What are the main performance differences between varchar and nvarchar SQL Server data types?
  5. How to get Time from DateTime format in SQL?
  6. SQL Server String or binary data would be truncated
  7. Datatype for storing ip address in SQL Server
  8. How to change identity column values programmatically?
  9. Why is it considered bad practice to use cursors in SQL Server?
  10. CAST and IsNumeric
  11. SQL Server pivot vs. multiple join
  12. SQL Query to find the last day of the month
  13. SQL Server, convert a named instance to default instance?
  14. SQL Server GUID sort algorithm. Why?
  15. WHERE clause on SQL Server “Text” data type
  16. SQL Server error on update command – “A severe error occurred on the current command”
  17. Parse XML in SQL Server
  18. SQL server schema and default schema
  19. SQL Server ON DELETE Trigger
  20. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  21. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  22. FOR XML PATH(”): Escaping “special” characters
  23. How to store directory / hierarchy / tree structure in the database?
  24. INFORMATION_SCHEMA vs sysobjects
  25. Does query plan optimizer works well with joined/filtered table-valued functions?
  26. “Prevent saving changes that require the table to be re-created” negative effects
  27. Convert UTC Milliseconds to DATETIME in SQL server
  28. Table Valued Function Killing My Query Performance
  29. Full text catalog/index search for %book%
  30. @@ERROR and/or TRY – CATCH

Leave a Comment