T-SQL – Best approach to remove the time portion of the datetime datatype

In the past, when I had a datetime datatype and I needed to show just the date portion, my best approach to remove the time portion of datetime datatype was converting to a character string. When you call the CONVERT function to change a DATETIME to a string (VARCHAR), it removes all time and allows you to format the date many different ways. For a complete list of date formats available to CONVERT, view the SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms187928.aspx)

Here are a couple examples to show how to format using CONVERT:


DECLARE @mydatetime DATETIME
SET @mydatetime = GETDATE()

SELECT CONVERT(VARCHAR,@mydatetime,1) AS [MMDDYY]
, CONVERT(VARCHAR,@mydatetime,101) AS [MMDDYYYY]
, CONVERT(VARCHAR,@mydatetime,103) AS [DDMMYYYY]

MMDDYY

MMDDYYYY

DDMMYYYY

03/19/14

03/19/2014

19/03/2014


DECLARE @mydatetime DATETIME
SET @mydatetime = GETDATE()

SELECT CONVERT(VARCHAR,@mydatetime,106) AS DayThreeMonthFourYear
, CONVERT(VARCHAR,@mydatetime,107) AS ThreeMonthDayFourYear
, CONVERT(VARCHAR,@mydatetime,108) AS TimePortionOfDate

DayThreeMonthFourYear

ThreeMonthDayFourYear

TimePortionOfDate

19 Mar 2014

Mar 19, 2014

16:17:15

The biggest drawback of using CONVERT to remove the time portion of the datetime datatype is that you lose the ability to use any of the date functions. For example, you cannot sort the column by calendar order, instead the sort will follow the numeric and alphabet sort order. Also, if you want to do date arithmetic, it does not work either. So, you end up calling the CONVERT function to make the datatype a string to remove the time and then CONVERT back to a datetime in order to use the date functions on the data. This is not very productive.

The best approach to remove the time portion of the datetime datatype is to use the DATEADD function.


DECLARE @mydatetime DATETIME
SET @mydatetime = GETDATE()

SELECT DATEADD(DAY,DATEDIFF(DAY,0,@mydatetime),0) AS Today
, DATEADD(DAY,DATEDIFF(DAY,0,@mydatetime),-1) AS Yesterday
, DATEADD(MONTH,DATEDIFF(MONTH,0,@mydatetime),0) AS FirstOfThisMonth
, DATEADD(MONTH,DATEDIFF(MONTH,-1,@mydatetime),-1) AS LastOfThisMonth
, DATEADD(MONTH,DATEDIFF(MONTH,0,@mydatetime),-1) AS LastOfPrevMonth

Today

Yesterday

FirstOfThisMonth

LastOfThisMonth

LastOfPrevMonth

2014-03-19

2014-03-18

2014-03-01

2014-03-31

2014-02-28

This will enable you to have great query performance by using a start date greater than “Today” and an end date less than “Tomorrow”. Instead of using the BETWEEN command or taking 1 second away from the end of today to get 24 hours.


DECLARE @mydatetime DATETIME
SET @mydatetime = GETDATE()

SELECT DATEADD(DAY,DATEDIFF(DAY,0,@mydatetime),0) AS Today
, DATEADD(DAY,DATEDIFF(DAY,0,@mydatetime),1) AS Tomorrow

SELECT *
FROM Invoices
WHERE CreatedDate >= DATEADD(DAY,DATEDIFF(DAY,0,@mydatetime),0)
AND CreatedDate < DATEADD(DAY,DATEDIFF(DAY,0,@mydatetime),1)