Date function

Hi, I am trying to retrieve all employees from the sample PUBS database with anniversary date within 90 days from today using the following statement:

SELECT fname, lname, hire_date
FROM EMPLOYEE
WHERE
DATEDIFF(yy, hire_date, GETDATE()+90) >
DATEDIFF(yy, hire_date, GETDATE())

but it does not return any result. How should I rewrite the statement?

use the following query..

Code Snippet

SELECT fname, lname, hire_date

FROM EMPLOYEE

WHERE

DATEDIFF(yy, hire_date, GETDATE()) <= 90

|||This is not quite what I needed. I want to find employees who would celebrate their anniversary date within the next 30 days.

For example, if today is 2007-05-29, the following employees would appear on the list:
John Doe, hire_date 1992-06-07
Jane Doe, hire_date 1995-06-25

The following employees would not appear on the list:
James Doe, hire_date 1996-05-28
Jill Doe, hire_date 1997-06-29

|||
SELECT fname
, lname
, hire_date
FROM EMPLOYEE
WHERE DATEPART(month,hire_date) BETWEEN DATEPART(month,GETDATE() AND DATEPART(month,DATEADD(day, 30, getdate()))|||

Ok.. Use the following query..

Code Snippet

Select *

From

(

SELECT

fname,

lname,

hire_date,

DateAdd(yy, Datediff(YY,hire_date,Getdate()),hire_date) CelebrateOn

FROM

EMPLOYEE

) as Data

Where

CelebrateOn Between

Cast(Convert(varchar,Getdate(),101) as Datetime) -- To eliminate the Time from Getdate()

And Cast(Convert(varchar,Getdate(),101) as Datetime) + 30 -- To eliminate the Time from Getdate()

|||i'm sorry, i think this one is better

SELECT fname
, lname
, hire_date
FROM EMPLOYEE
WHERE DATEADD(year,DATEDIFF(year,hire_date,GETDATE()),hire_date) BETWEEN GETDATE() AND DATEADD(day, 30, getdate())|||

Very nicely done Rhamille.

I think that if you make the following modification, the query will be able to use any indexing on HireDate. (The way it is currently written would negate the possibility of using indexing.

(This example uses AdventureWorks.)

Code Snippet

SELECT
e.EmployeeID,
c.FirstName,
c.LastName,
e.HireDate,
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.contactID
WHERE e.HireDate BETWEEN ( dateadd( year, datediff( year, getdate(), e.HireDate ), getdate()))
AND ( dateadd( year, datediff( year, getdate(), e.HireDate ), getdate()) + 30 )

|||

Arnie Rowland wrote:

Very nicely done Rhamille.

I think that if you make the following modification, the query will be able to use any indexing on HireDate. (The way it is currently written would negate the possibility of using indexing.

(This example uses AdventureWorks.)

Code Snippet

SELECT
e.EmployeeID,
c.FirstName,
c.LastName,
e.HireDate,
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.contactID
WHERE e.HireDate BETWEEN ( dateadd( year, datediff( year, getdate(), e.HireDate ), getdate()))
AND ( dateadd( year, datediff( year, getdate(), e.HireDate ), getdate()) + 30 )

good idea arnie.. instead of moving the HireDate to the present date, we should move the current date back to year of the HireDate, so that HireDate can be indexed. i'll take note of this one.. and make it to practice of always considering indexed columns |||Thanks, Rhamille, Arnie. Both work as intended.

|||This query rewrite will not help if there is an index on HireDate. You still reference the column on both sides of the predicate. You need to have at least some known value on the right-hand side or column from another table for this to work. The above WHERE clause needs to look at every row due to reference to HireDate on either sides of the predicate.|||

Umachandar,

Initially, I had the same impression as you, that having the HireDate field on the right side of the predicate would be significantly different than having a constant value. However, the execution plans are the same in both situations -both require an index scan and seek. (You'll notice that I used both a variable and a constant value on the right side of the predicate.

So it seems to me that there is no penalty for using the HireDate field on the right side of the predicate. It still requires an index scan/seek operation. Granted, there will be a small additional effort to apply the function to the HireDate value as the scan is occurring.

Perhaps I'm missing something...

StmtText
-
SELECT
e.EmployeeID,
c.FirstName,
c.LastName,
e.HireDate
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.contactID
WHERE e.HireDate BETWEEN ( dateadd( year, datediff( year, getdate(), e.HireDate ), getdate()))
AND ( dateadd( year, datediff( year, getdate(), e.HireDate ), getdate()) + 30 )

StmtText
-
|--Nested Loops(Inner Join, OUTER REFERENCES.[ContactID], [Expr1008]) WITH UNORDERED PREFETCH)
Loops(Inner Join, OUTER REFERENCES.[ContactID], [Expr1008]) WITH UNORDERED PREFETCH)
|--Clustered Index Scan(OBJECT[AdventureWorks].[HumanResources].[Employee].[PK_Employee_EmployeeID] AS ), WHERE[AdventureWorks].[HumanResources].[Employee].[HireDate] as .[HireDate]>=dateadd(year,datediff(year,getdate(),[AdventureWorks].[HumanResources].[Employee].[HireDate] as .[HireDate]),getdate()) AND [AdventureWorks].[HumanResources].[Employee].[HireDate] as .[HireDate]<=(dateadd(year,datediff(year,getdate(),[AdventureWorks].[HumanResources].[Employee].[HireDate] as .[HireDate]),getdate())+'1900-01-31 00:00:00.000')))
|--Clustered Index Seek(OBJECT[AdventureWorks].[Person].[Contact].[PK_Contact_ContactID] AS ), SEEK.[ContactID]=[AdventureWorks].[HumanResources].[Employee].[ContactID] as .[ContactID]) ORDERED FORWARD)

StmtText
-
DECLARE @.HireDate datetime

SET @.HireDate = '1999/06/04'

SELECT
e.EmployeeID,
c.FirstName,
c.LastName,
e.HireDate
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.contactID
WHERE e.HireDate BETWEEN @.HireDate
AND @.HireDate + 30

StmtText
-
|--Nested Loops(Inner Join, OUTER REFERENCES.[ContactID], [Expr1005]) WITH UNORDERED PREFETCH)
|--Clustered Index Scan(OBJECT[AdventureWorks].[HumanResources].[Employee].[PK_Employee_EmployeeID] AS ), WHERE[AdventureWorks].[HumanResources].[Employee].[HireDate] as .[HireDate]>=[@.HireDate] AND [AdventureWorks].[HumanResources].[Employee].[HireDate] as .[HireDate]<=[@.HireDate]+'1900-01-31 00:00:00.000'))
|--Clustered Index Seek(OBJECT[AdventureWorks].[Person].[Contact].[PK_Contact_ContactID] AS ), SEEK.[ContactID]=[AdventureWorks].[HumanResources].[Employee].[ContactID] as .[ContactID]) ORDERED FORWARD)

StmtText
-
SELECT
e.EmployeeID,
c.FirstName,
c.LastName,
e.HireDate
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.contactID
WHERE e.HireDate BETWEEN '1999/06/04'
AND '1999/07/04'

StmtText
-
|--Nested Loops(Inner Join, OUTER REFERENCES.[ContactID]))
|--Clustered Index Scan(OBJECT[AdventureWorks].[HumanResources].[Employee].[PK_Employee_EmployeeID] AS ), WHERE[AdventureWorks].[HumanResources].[Employee].[HireDate] as .[HireDate]>='1999-06-04 00:00:00.000' AND [AdventureWorks].[HumanResources].[Employee].[HireDate] as .[HireDate]<='1999-07-04 00:00:00.000'))
|--Clustered Index Seek(OBJECT[AdventureWorks].[Person].[Contact].[PK_Contact_ContactID] AS ), SEEK.[ContactID]=[AdventureWorks].[HumanResources].[Employee].[ContactID] as .[ContactID]) ORDERED FORWARD)