Conditional Date Select

[byrne1]

I am trying to pull records that are at least 7.5 years old but I can't get my stored procedure to work right. Can someone please help me? My stored procedure is as follows:

SELECT max(inout.inout_date) as 'lastdate', inout.account_number, loans.short_name from inout
INNER JOIN loans ON inout.account_number=loans.account_number
WHERE datediff(mm, 'lastdate',getdate()) >=111
GROUP BY inout.account_number, loans.short_name


The error message that I'm getting is:
Syntax error converting datetime from character string.

(inout.inout_date is a datetime field)

 

[aalmeida]

I think that 7.5 year = 90 months because 1 year has 12 months 7.5*12=90.
The following query works for me
SELECT max(i.inout_date) as 'lastdate',
i.account_number,
l.short_name
FROM inout i INNER JOIN loans l ON
i.account_number = l.account_number
WHERE datediff(mm, i.inout_date,getdate())>=90
GROUP BY i.account_number, l.short_name
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[foxdev]

Byrne1, I think your core problem was that you had 'lastdate' in quotes in your DateDiff function, and SQLS was reasonably interpreting that as a string, not as a column name. Robert Bradley

http://www.agrainofhope.org

http://www.lowcostprints.com

http://www.foxdev.com

 

[tlbroadbent]

Adding to Robert's explanation, T-SQL doesn't allow you to the alias later in the query as can be done in MS Access. 'Latest' is an alias and as AL Almeida showed in his query, you can use the date column, "i.inout_date" in the Datediff function. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[byrne1]

Thanks to each of you for your help. Also, thanks for the math lesson. It's just been one of those days.