Using DATEDIFF in T-SQL

Question

I am using DATEDIFF in an SQL statement. I am selecting it, and I need to use it in WHERE clause as well. This statement does not work...

SELECT DATEDIFF(ss, BegTime, EndTime) AS InitialSave
FROM MyTable
WHERE InitialSave <= 10

It gives the message: Invalid column name "InitialSave"

But this statement works fine...

SELECT DATEDIFF(ss, BegTime, EndTime) AS InitialSave
FROM MyTable
WHERE DATEDIFF(ss, BegTime, EndTime) <= 10

The programmer in me says that this is inefficient (seems like I am calling the function twice).

So two questions. Why doesn't the first statement work? Is it inefficient to do it using the second statement?

Answer 1

You have to use the function instead of the column alias - it is the same with count(*), etc. PITA.

Answer 2

As an alternate, you can use computed columns.

Answer 3

You can't access columns defined in the select statement in the where statement, because they're not generated until after the where has executed.

You can do this however

select InitialSave from 
(SELECT DATEDIFF(ss, BegTime, EndTime) AS InitialSave
FROM MyTable) aTable
WHERE InitialSave <= 10

As a sidenote - this essentially moves the DATEDIFF into the where statement in terms of where it's first defined. Using functions on columns in where statements causes indexes to not be used as efficiently and should be avoided if possible, however if you've got to use datediff then you've got to do it!

Answer 4

You may find this to be a better performer. Especially if you have an index on one of those columns.

SELECT DATEDIFF(ss, BegTime, EndTime) AS InitialSave
FROM MyTable
WHERE EndTime <= BegTime + '00:00:10'

If your index is on BegTime, it may help to move it to be alone on its side of the comparison.

UPDATE

I should also point out that doing a DateDiff like this will not return the elapsed time but counts the number of boundaries crossed. If DateDiff returns one second, this could mean 3 ms, or it could mean 1997 ms (assuming datetime data type), as long as it crosses a whole second boundary. For more accurate counting, you would want the following query:

SELECT DATEDIFF(ss, 0, EndTime - BegTime) AS InitialSave
FROM MyTable
WHERE EndTime <= BegTime + '00:00:10'

This now has only a rounding error of one second (in effect, a floor() operation).

This principle is especially important when counting larger units such as hours or days.

Answer 5

beyond making it "work", you need to use an index

use a computed column with an index, or a view with an index, otherwise you will table scan. when you get enough rows, you will feel the PAIN of the slow scan!

computed column & index:

ALTER TABLE MyTable ADD
    ComputedDate  AS DATEDIFF(ss,BegTime, EndTime)
GO
CREATE NONCLUSTERED INDEX IX_MyTable_ComputedDate  ON MyTable 
    (
    ComputedDate
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

create a view & index:

CREATE VIEW YourNewView
AS
SELECT
    KeyValues
        ,DATEDIFF(ss, BegTime, EndTime) AS InitialSave
    FROM MyTable
GO
CREATE CLUSTERED INDEX IX_YourNewView
    ON YourNewView(InitialSave)
GO