Best approach to remove time part of datetime in S

Question

Which method provides the best performance when removing the time portion from a datetime field in SQL Server?

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

or

b) select cast(convert(char(11), getdate(), 113) as datetime)

The second method does send a few more bytes either way but that might not be as important as the speed of the conversion.

Both also appear to be very fast, but there might be a difference in speed when dealing with hundreds-of-thousands or more rows?

Also, is it possible that there are even better methods to get rid of the time portion of a datetime in SQL?

Answer 1

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

Answer 2

I really like:

[date] = CONVERT(VARCHAR(10), GETDATE(), 120)

The 120 format code will coerce the date into the ISO 8601 standard:

'YYYY-MM-DD' or '2017-01-09'

Super easy to use in dplyr (R) and pandas (Python)!

Answer 3

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.

Answer 4

For me the code below is always a winner:

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())));

Answer 5

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

Answer 6

In SQL Server 2008, you can use:

CONVERT(DATE, getdate(), 101)