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 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/20014
08/08/200412
02/21/19978
02/19/198817
07/12/200410
01/17/19978
06/22/199411
06/27/19978
05/24/20046
01/20/20014
07/12/20046
06/01/200424
02/20/200413
01/01/200512
11/18/19978
04/19/20014
08/25/19996
03/22/200429
12/01/199411
Any suggestions why 2004 and 2005 produce wrong results? Thanks
Hi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is related
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 these
> 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/20014
> 08/08/200412
> 02/21/19978
> 02/19/198817
> 07/12/200410
> 01/17/19978
> 06/22/199411
> 06/27/19978
> 05/24/20046
> 01/20/20014
> 07/12/20046
> 06/01/200424
> 02/20/200413
> 01/01/200512
> 11/18/19978
> 04/19/20014
> 08/25/19996
> 03/22/200429
> 12/01/199411
> Any suggestions why 2004 and 2005 produce wrong results? Thanks