In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE()
but would like it to have a time of 00:00:00.0
SQL Server – How to Get Current Date Without Time Part?
sql serversql-server-2005
Related Question
- Sql-server – Get minute part from time data type
- Sql-server – more concise way to convert a UTC datetime into a local date only
- SQL Server – How to Get Correct Offset Between UTC and Local Times
- Sql-server – Average, between specific date/time, of the difference between current field and previous field
- SQL Server Employee Names by Date – How to Retrieve
- SQL Server – How to Extract Time from Date into New Column
- SQL Server – Using DATE Part of DATETIME Column in WHERE Clause
- SQL Server – CURRENT_DATE or Current Date Value Function
Best Answer
The fastest if you have to iterate over a recordset and don't have date in SQL Server 2008
Two different and excellent answers on StackOverflow bear this out: One, Two
Varchar conversions are one of the worst ways to do it. Of course, for one value it may not matter but it's a good habit to get into.
This way is also deterministic, say if you want to index a computed column. Even folk who write books about SQL Server get caught out with datetime conversions
This technique is also extendable.
DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)
Edit:
As I mentioned about determinism, varchar methods are not safe unless you use style 112.
Other answers here point out that you'd never apply this to a column. This is correct, but you may want to select a 100k rows or add a computed column or GROUP BY dateonly. Then you have to use this method.
The other answer also mentions style 110. This is not language or SET DATEFORMAT safe and fails with "british" language setting. See the ultimate guide to the datetime datatypes by Tibor Karaszi.