Remove time from datetime in SQL

/, SQL Server/Remove time from datetime in SQL

There are two ways to remove the time from the datetime datatype.

The first method is using the dateadd and datediff system functions and the second method is to convert from datetime to characters.

--First Method
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
--Second Method
SELECT CONVERT(CHAR(11), GETDATE(), 113)

The second method converting the date time to characters is subject to date format issues.

For SQL Server 2008 and later you can simply cast the field to date data type to remove the time from the datetime. Be careful, if you are going to add a function or casting in the where clause as this will invalidate the index usage.

Below is the script for the user defined function

CREATE FUNCTION [dbo].[udf_RemoveTimeFromDate] (@Date AS DATETIME) 
RETURNS DATETIME
AS
  BEGIN
		RETURN (SELECT DATEADD(dd, DATEDIFF(dd, 0, @Date), 0))
  END 
2017-03-05T13:54:56+00:00 January 10th, 2017|SQL, SQL Server|0 Comments

Leave A Comment