DateDiff function
-
I'm a newbie at T-SQL and have some questions as to how datediff works in a specific query. Here is the query:
Select fname, lname, hire_date
From employee
Where Datediff(yy, hire_date, getdate()+30) > datediff(yy, hire_date, getdate())
This query, when run against the pubs database, returns an empty result set. But, there are hire dates within the 30 day time frame that should show. If I change the startdate parameter from years to days, months, or day of the year it returns all hiredates. I can see why too, but not why it returns the empty result set using the yy parameter. If I change the +30 to a +90 it once again returns all hiredates using the year parameter.
Can anyone explain this to me? I really hate to get off on the wrong foot and not understand these basic issues.
-
Trouble is, DATEDIFF( YY (or YEAR) ... just executes a DATEPART( YEAR, <second date> - DATEPART( YEAR, <first date> )
You may want to check a DATEDIFF( MONTH,... which is more of what you expect to see. Why the two work differently is a mystery to me as well.
Guarddata-
-
I think if you break this down as a select statement you will see why it does this. try running the following:
Select fname
, lname
, hire_date
, getdate()
, 'Years_Worked_Today' = datediff(yy, hire_date, getdate())
, 'Years_Worked_In_30_Days' = Datediff(yy, hire_date, getdate()+30)
, 'Years_Worked_In_60_Days' = Datediff(yy, hire_date, getdate()+60)
, 'Years_Worked_In_90_Days' = Datediff(yy, hire_date, getdate()+90)
, 'Diff_Today_to_30Days' = (Datediff(yy, hire_date, getdate() + 30) - datediff(yy, hire_date, getdate()))
, 'Diff_Today_to_60Days' = (Datediff(yy, hire_date, getdate() + 60) - datediff(yy, hire_date, getdate()))
, 'Diff_Today_to_90Days' = (Datediff(yy, hire_date, getdate() + 90) - datediff(yy, hire_date, getdate()))
From employee
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Gary Johnson,
Thanks. That made it make sense. It is looking only at the year, not the anniversary month-day-year for calculating years of employment, because after 90 days the year changes. I wondered about that when I saw that the yy parameter was used for years. I just wasn't sure. The whole problem with this is that the original query was, according to the author of the book I'm using, supposed to be able to find all anniversary dates within the next 30 days. It obviously doesn't do that.
Can you recommend a good book on T-SQL for me? I no longer trust the one I have.
-
The problem with using DATEDIFF() for you criteria is that DATEDIFF will round to the nearest number, and so cannot be used in this way, for example, if you run the following in Query Analyzer:
PRINT DATEDIFF(yy, '2002-11-30', '2003-12-15')
PRINT DATEDIFF(yy, '2002-11-30', '2003-11-15')
PRINT DATEDIFF(yy, '2002-11-30', '2003-10-15')
Your output will be:
1
1
1
If you are working in days difference then your functions should work also in days, e.g.
WHERE (DATEDIFF(d, hire_date, GETDATE()) % 365) > 335 -- That's 365 - 30 days
But this doesn't account for leap years, so your longer server employees anniversaries would be a few days off!
Edited by - ed harling on 10/15/2003 02:41:18 AM
-
SELECT FName, LName, Hire_Date
FROM Employee
WHERE DATEDIFF(dd,GETDATE(),DATEADD(yy,DATEDIFF(yy,Hire_Date,GETDATE()),Hire_Date)) BETWEEN 0 AND 30--Jonathan
--Jonathan -
Just wanted to clarify how DATEDIFF actually works. All DATEDIFF does is count the number of boundries crossed between the given dates.
DATEDIFF(yy, '12/31/2000', '1/1/2001') = 1
DATEDIFF(yy, '01/01/2000', '1/1/2001') = 1
In both cases only one year BOUNDRY was crossed.
-
quote:
SELECT FName, LName, Hire_Date
FROM Employee
WHERE DATEDIFF(dd,GETDATE(),DATEADD(yy,DATEDIFF(yy,Hire_Date,GETDATE()),Hire_Date)) BETWEEN 0 AND 30--Jonathan
I do not think this accounts for the case when the 30-day period crosses the year boundary.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
-- This is not elegant.
declare @HireDate as datetime
set @HireDate = '1/1/1990'
-- Pre-calculation
declare @Now as datetime
declare @NowPlus30Days as datetime
declare @dy1 as int, @dy2 as int -- for the day of year interval
set @Now = '12/08/2003' --GetDate()
set @NowPlus30Days = DATEADD(dd, 30, @Now)
set @dy1 = DATEPART(dy, @Now)
set @dy2 = DATEPART(dy, @NowPlus30Days)
select DATEPART(dy, @HireDate) as [hire_dy], @dy1 as [@dy1], @dy2 as [@dy2]
-- The query changes if the 30-day period crosses the year end boundary
if @dy1 < @dy2
SELECT 'Yes'
WHERE DATEPART(dy, @HireDate) BETWEEN @dy1 AND @dy2
else
SELECT 'Yes'
WHERE DATEPART(dy, @HireDate) NOT BETWEEN @dy2 AND @dy1
-- Or, if you want one query...
SELECT 'Yes'
WHERE (@dy1 < @dy2 AND DATEPART(dy, @HireDate) BETWEEN @dy1 AND @dy2)
OR (@dy2 < @dy1 AND DATEPART(dy, @HireDate) NOT BETWEEN @dy2 AND @dy1)
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
Thanks for the help guys. I appreciate the help. I now have a much better idea about how DateDiff works, and when and where to use it.
-
Eureka! This was really bugging me, as I couldn't figure out why this was so difficult to do.
Concisely, find records where the NEXT Anniversary date is between the currentdate and currentdate + 30.
The trick to finding the next anniversary is checking the hire month and day against the current month and day, then adding 1 year if lower.
This works:
declare @getdate-2 datetime
set @getdate-2 = getdate() + 60
Select hire_Date,
Case
When dateadd(yy, datediff(yy, hire_date, @getdate-2), hire_date) > @getdate-2 then dateadd(yy, datediff(yy, hire_date, @getdate-2), hire_date)
Else dateadd(yy, datediff(yy, hire_date, @getdate-2) + 1, hire_date)
END
From employee
where
Case
When dateadd(yy, datediff(yy, hire_date, @getdate-2), hire_date) > @getdate-2
then dateadd(yy, datediff(yy, hire_date, @getdate-2), hire_date)
Else dateadd(yy, datediff(yy, hire_date, @getdate-2) + 1, hire_date)
END between @getdate-2 and @getdate-2 + 30
Signature is NULL
-
quote:
quote:
SELECT FName, LName, Hire_Date
FROM Employee
WHERE DATEDIFF(dd,GETDATE(),DATEADD(yy,DATEDIFF(yy,Hire_Date,GETDATE()),Hire_Date)) BETWEEN 0 AND 30--Jonathan
I do not think this accounts for the case when the 30-day period crosses the year boundary.
You're right; thanks!
Looks like we've got to use a CASE in this case.
SELECT FName, LName, Hire_Date
FROM Employee
WHERE DATEDIFF(dd,GETDATE(),DATEADD(yy,DATEDIFF(yy,Hire_Date,GETDATE()) + CASE
WHEN DATEPART(y,Hire_Date) < DATEPART(y,GETDATE()) THEN 1
ELSE 0 END,Hire_Date)) BETWEEN 0 AND 30Note that I'm using DATEDIFF() in order to obviate issues with the time components.
--Jonathan
--Jonathan -
getdate() = '27/04/2005'
select DATEDIFF(year,cast('19870129' as datetime) , getdate())
18 - wrong way
select DATEDIFF(year,'1900-01-01' ,getdate()-cast('19870129' as datetime))
17 - right way
-
For lots of great info on this and more check out Frank Kalis' excellent article on SQl Server dates at
http://www.sql-server-performance.com/fk_datetime.asp
-
Hi this covers all cases including crossing a year boundry.
HTH
Mike
/*
Find all aniversity dates that will fall within the next 30 days
Condiitons
EE must have been employed with the company at least 1 year
The start date must be between today and 30 days from today
Note that adding 30 days to the current date may not take you into
a new month. Jan 1 yyyy + 30 days = Jan 31 yyyy
Returns Employee ID, Employee Start Date, The Aniversity being honored
*/
Create Table #T
(
EEID int,
StartDate DateTime
)
INSERT INTO #T (EEID,StartDate)
SELECT 1,'1/1/2005' UNION
SELECT 2,'2/1/2005' UNION
SELECT 3,'3/1/2005' UNION
SELECT 4,'4/1/2005' UNION
SELECT 4,'4/26/2005' UNION
SELECT 4,'4/27/2004' UNION
SELECT 5,'5/1/2005' UNION
SELECT 15,'5/22/2005'UNION
SELECT 16,'5/2/2004'UNION
SELECT 6,'6/1/2005' UNION
SELECT 7,'7/1/2005' UNION
SELECT 8,'8/1/2005' UNION
SELECT 9,'9/1/2005' UNION
SELECT 10,'10/1/2005' UNION
SELECT 11,'11/1/2005' UNION
SELECT 17,'4/29/2001' UNION
SELECT 18,'5/21/1995' UNION
SELECT 19,'5/5/1940' UNION
SELECT 13,'12/23/2005'UNION
SELECT 20,'1/1/05'
/*
Find all aniversity dates that will fall within the next 30 days
Condiitons
EE must have been employed with the company at least 1 year
The start date must be between today and 30 days from today
Note that adding 30 days to the current date may not take you into
a new month. Jan 1 yyyy + 30 days = Jan 31 yyyy
Returns Employee ID, Employee Start Date, The Aniversity being honored
*/
Create Table #T
(
EEID int,
StartDate DateTime
)
INSERT INTO #T (EEID,StartDate)
SELECT 1,'1/1/2005' UNION
SELECT 2,'2/1/2005' UNION
SELECT 3,'3/1/2005' UNION
SELECT 21,'4/1/2005' UNION
SELECT 22,'4/26/2004' UNION
SELECT 23,'4/30/2004' UNION
SELECT 5,'5/1/2005' UNION
SELECT 15,'5/22/2005'UNION
SELECT 16,'5/2/2004'UNION
SELECT 6,'6/1/2005' UNION
SELECT 7,'7/1/2005' UNION
SELECT 8,'8/1/2005' UNION
SELECT 9,'9/1/2005' UNION
SELECT 10,'10/1/2005' UNION
SELECT 11,'11/1/2005' UNION
SELECT 17,'4/29/2001' UNION
SELECT 18,'5/21/1995' UNION
SELECT 19,'5/5/1940' UNION
SELECT 13,'12/23/2005'UNION
SELECT 20,'1/1/05'
SELECT s.EEID,
Convert(char(12),s.StartDate)AS "Employed On",
DATEDIFF ( yy , StartDate , GetDate() ) AS Aniversity
FROM #T AS S
WHERE DatePart(mm,S.StartDate) = DatePart(mm,GetDate())
AND
DatePart(yy,S.StartDate)< DatePart(yy,GetDate())
AND
DatePart(dd,S.Startdate) >= DatePart(dd,GetDate())
OR
DatePart(mm,S.StartDate) = DatePart(mm,GetDate()+30)
AND
DatePart(yy,S.StartDate)< DatePart(yy,GetDate())
--Check case where year changes
--Same as above statement with slight mod to accomidate forcing 'Current Date ' to Dec
SELECT s.EEID,
Convert(char(12),s.StartDate)AS "Employed On"
FROM #T AS S
WHERE DatePart(mm,S.StartDate) = 12
AND
DatePart(yy,S.StartDate)< Cast('2005' AS DATETIME)
OR
DatePart(mm,S.StartDate) = 1
AND
DatePart(yy,S.StartDate)< CAST('2005' AS DATETIME)
Drop Table #T
Drop Table #T
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply