Anatoly Lubarsky

Ayende posted one. Since I like this kind of tricks in T-SQL I just thought to post kinda the same just make it more simple. Enjoy :)

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Related Posts:

• T-SQL: Date Ranges
• Date Processing - T-SQL or SQLCLR ?
• MSSQL Dynamic Management Views (DMVs) for indexes
• MSSQL 2005 System Tables Map
• MSSQL: How to migrate a database server
• MSSQL: Partitioning and Parallelism
• MSSQL 2005: Troubleshooting Performance methodology
• T-SQL: Paging with ROW_NUMBER()
• Show MSSQL Query Estimated Execution Plan in XML Format (SET showplan_xml ON)
• T-SQL 2005: One-to-many comma separated

Another way to accomplish just the same is
declare @date datetime
set @date=CONVERT(varchar(8), GETDATE(), 112)
Found at http://www.sql.ru/faq/faq_topic.aspx?fid=110

bazile: Yep, it is well known one.

It has some disadvantages. The biggest disadvantage is that you have implicit conversion from VARCHAR to DATETIME - so pay attention you can get a different date than the one you are working with.

I was sure that this method is 100% accurate. Result of CONVERT(varchar(8), GETDATE(), 112) should be treated equally on all versions of SQL Server with any localization. Date format of 'yyyymmdd' is Microsoft's recommended way of representing date as a string. So i thought. Can you give an example when this method will not work as intented?

hm... Actually this way is ok. It is also a docummented one, but...

I don't like it because IMHO it just can not be a good way to convert datetime to a string and convert it back just to get datetime. You have one explicit conversion and one implicit which is especially not good for SQL.

Implicit conversion takes place here:
@date = VARCHAR

Actually my way is also a bit more performant. But conversion will be a pain in queries (WHERE clause, GROUP BY clause, etc)

I've posted about the same subject some time ago and post is available at http://weblogs.asp.net/guerchele/archive/2004/05/04/125899.aspx

Regards,

Luciano Evaristo Guerche
Taboão da Serra, SP, Brazil

the most effective way:

function FixDate(@dt as datetime) returns datetime
begin
return cast(cast((@dt - 0.500000038580247) as int) as datetime)
end

andrey: No, the most effective way is the one in the post.

JustDate: Striping the time part from datetime variables

Sometimes it is useful for social oriented networks to calculate on the fly or store user age given his/her date of birth. A small trick [ ... ]