Why would someone use this?

Question

I came across this line of code in a SQL Report I am trying to fix. Could someone tell me what is the purpose of this.

DATEADD(dd, - DATEDIFF(dd, d.TxnDateTime, 1), 1) As TxnDate

To me it seems like it would cancel itself out.

Answer 1

It's removing the TIME portion of the DATETIME without changing the data type. You can observe the different behavior here: SQL Fiddle

Again, not sure why you'd need to preserve the DATETIME type while removing the time, unless it predates the DATE datatype.

Answer 2

The Date data type was added in SQL 2008 per MSDN. Before SQL 2008, this formula was one way to truncate the time from a DateTime variable.

Answer 3

It was probably written for SQL Server 2005, when CONVERT(DATE was just a glimmer in some Microsoft employee's eye, and when we had to use cumbersome, inefficient and hard-to-explain workarounds to remove the time portion of a DATETIME.

People still use those cumbersome, efficient, and hard-to-explain methods, of course. But I don't think anyone here can tell you why, especially if you're looking for the reason that particular developer chose that particular format in that particular case. We simply can't speak for them. Maybe they stole it from somewhere else, maybe it actually makes sense to them, maybe they just plugged it in without even knowing what it does.

Today, the better approach is:

CONVERT(DATE, d.TxnDateTime);

...and I have demonstrated this many times, including here and here.

Now, if you are trying to get all the rows where d.TxnDateTime falls on a specific day, a much better approach is to use a DATE parameter, and an open-ended range query:

WHERE d.TxnDateTime >= @ThatDay
  AND d.TxnDateTime <  DATEADD(DAY, 1, @ThatDay);

This is superior to:

WHERE CONVERT(DATE, d.TxnDateTime) = @ThatDay;

Because, while sargable, that expression can still lead to rather poor cardinality estimates. For more information see this very thorough post:

https://dba.stackexchange.com/questions/34047/cast-to-date-is-sargable-but-is-it-a-good-idea

Also might not be a bad idea to read this one:

https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries

And also this regarding dd:

https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations

Answer 4

It appears the previous code author was attempting to cast a DATETIME object to a DATE object... basically removing the time component (HH:MM:SS). A more common way to to this is:

SELECT CAST(d.TxnDateTime AS DATE) AS [TxnDate]