T-SQL: Get only Date from DateTime

How to get only DATE portion out of DATETIME column in MSSQL ?

Ayende just posted a T-SQL function that strips DATE from DATETIME value. Since I like T-SQL for this kind of tricks I just decided to post kinda the same snippet 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.

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 [ ... ]

MSSQL 2008 introduces several new DATETIME datatypes for T-SQL: TIME, DATE, DATETIME2, DATETIMEOFFSET. It is obvious that TIME and DATE provide the ability to store separately DATE and TIME parts of DATETIME. It was not possible to do in previous versions of MSSQL, however [ ... ]

it's the worse topic i had seen

You can use SELECT CONVERT(CHAR(10),GETDATE(),103)

You will get month/day/year format.
If you want different format change 103 to 101 or some other from 101 to 113

Replace GETDATE() with your date column.

@Sunilkumar

the point is to get DATETIME not CHAR/VARCHAR

In SQL 2008, CONVERT(DATE,GETDATE()) should do it.

You never want to convert a date to something like a varchar. My sollution is in my opinion better:

dateadd(dd, -datediff(dd, <mydatecolumn>, 1), 1)

I just get the number of days in the date since 'date 1' and add it to 'date 1'. You get a clean new date where the time component is 0.

You really don't want to CAST or CONVERT. This is about as simple as it gets.

DECLARE @Today DATETIME
SET @Today = DATEDIFF(dd, 0, GETDATE())

Getting date from SQL datetime type | Cogent Code