Best approach to remove time part of datetime in SQL Server

I, personally, almost always use [User Defined functions][1] for this if dealing with SQL Server 2005 (or lower version), however, it should be noted that there are specific drawbacks to using UDF's, especially if applying them to WHERE clauses (see below and the comments on this answer for further details). If using SQL Server 2008 (or higher) - see below.

In fact, for most databases that I create, I add these UDF's in right near the start since I know there's a 99% chance I'm going to need them sooner or later.

I create one for "date only" & "time only" (although the "date only" one is by far the most used of the two).

Here's some links to a variety of date-related UDF's:

[Essential SQL Server Date, Time and DateTime Functions][2]
[Get Date Only Function][3]

That last link shows no less than 3 different ways to getting the date only part of a datetime field and mentions some pros and cons of each approach.

If using a UDF, it should be noted that you should try to avoid using the UDF as part of a WHERE clause in a query as this will greatly hinder performance of the query. The main reason for this is that using a UDF in a WHERE clause renders that clause as [non-sargable][4], which means that SQL Server can no longer use an index with that clause in order to improve the speed of query execution. With reference to my own usage of UDF's, I'll frequently use the "raw" date column within the WHERE clause, but apply the UDF to the SELECTed column. In this way, the UDF is only applied to the filtered result-set and not every row of the table as part of the filter.

Of course, the absolute *best* approach for this is to use SQL Server 2008 (or higher) and separate out your [dates and times][5], as the SQL Server database engine is then natively providing the individual date and time components, and can efficiently query these independently without the need for a UDF or other mechanism to extract either the date or time part from a composite datetime type.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

[5]:

[To see links please register here]

I would use:

CAST
(
CAST(YEAR(DATEFIELD) as varchar(4)) + '/' CAST(MM(DATEFIELD) as varchar(2)) + '/' CAST(DD(DATEFIELD) as varchar(2)) as datetime
)

Thus effectively creating a new field from the date field you already have.

BEWARE!

Method a) and b) does NOT always have the same output!

select DATEADD(dd, DATEDIFF(dd, 0, '2013-12-31 23:59:59.999'), 0)

Output: `2014-01-01 00:00:00.000`

select cast(convert(char(11), '2013-12-31 23:59:59.999', 113) as datetime)

Output: `2013-12-31 00:00:00.000`

(Tested on MS SQL Server 2005 and 2008 R2)

EDIT: According to Adam's comment, this cannot happen if you read the date value from the table, but it can happen if you provide your date value as a literal (example: as a parameter of a stored procedure called via ADO.NET).

select CONVERT(char(10), GetDate(),126)

How about `select cast(cast my_datetime_field as date) as datetime)`? This results in the same date, with the time set to 00:00, but avoids any conversion to text and also avoids any explicit numeric rounding.

In SQL Server 2008, there is a DATE datetype (also a TIME datatype).

CAST(GetDate() as DATE)

or

declare @Dt as DATE = GetDate()

Of-course this is an old thread but to make it complete.

From SQL 2008 you can use DATE datatype so you can simply do:

SELECT CONVERT(DATE,GETDATE())

In SQL Server 2008, you can use:

<!-- language: lang-sql -->

CONVERT(DATE, getdate(), 101)

I think that if you stick strictly with `TSQL` that this is the fastest way to truncate the time:

select convert(datetime,convert(int,convert(float,[Modified])))

I found this truncation method to be about 5% faster than the `DateAdd` method. And this can be easily modified to round to the nearest day like this:



select convert(datetime,ROUND(convert(float,[Modified]),0))

Here I made a function to remove some parts of a datetime for SQL Server. Usage:

- First param is the datetime to be stripped off.
- Second param is a char:
- s: rounds to seconds; removes milliseconds
- m: rounds to minutes; removes seconds and milliseconds
- h: rounds to hours; removes minutes, seconds and milliseconds.
- d: rounds to days; removes hours, minutes, seconds and milliseconds.
- Returns the new datetime


`create function dbo.uf_RoundDateTime(@dt as datetime, @part as char)
returns datetime
as
begin
if CHARINDEX( @part, 'smhd',0) = 0 return @dt;
return cast(
Case @part
when 's' then convert(varchar(19), @dt, 126)
when 'm' then convert(varchar(17), @dt, 126) + '00'
when 'h' then convert(varchar(14), @dt, 126) + '00:00'
when 'd' then convert(varchar(14), @dt, 112)
end as datetime )
end`