DateDiff returns odd result

I have a table of addresses for residents in a housing authority, and these
addresses may change so I need to find the first move in date and subtract i
t
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/2001 4
08/08/2004 12
02/21/1997 8
02/19/1988 17
07/12/2004 10
01/17/1997 8
06/22/1994 11
06/27/1997 8
05/24/2004 6
01/20/2001 4
07/12/2004 6
06/01/2004 24
02/20/2004 13
01/01/2005 12
11/18/1997 8
04/19/2001 4
08/25/1999 6
03/22/2004 29
12/01/1994 11
Any suggestions why 2004 and 2005 produce wrong results? ThanksHi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is relate
d
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:

> I have a table of addresses for residents in a housing authority, and thes
e
> addresses may change so I need to find the first move in date and subtract
it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/2001 4
> 08/08/2004 12
> 02/21/1997 8
> 02/19/1988 17
> 07/12/2004 10
> 01/17/1997 8
> 06/22/1994 11
> 06/27/1997 8
> 05/24/2004 6
> 01/20/2001 4
> 07/12/2004 6
> 06/01/2004 24
> 02/20/2004 13
> 01/01/2005 12
> 11/18/1997 8
> 04/19/2001 4
> 08/25/1999 6
> 03/22/2004 29
> 12/01/1994 11
> Any suggestions why 2004 and 2005 produce wrong results? Thanks