Problem in HAVING Clause with Date

[NeoPa]

I have some SQL which runs fine without the HAVING clause, yet when I uncomment either of the HAVING clause versions in the SQL it errors.

SELECT   TOP 10
         [OH_LEDGER]
        ,CAST(Max([OH_ENTERED_DATE]) AS DateTime) AS [MaxDate]
 
FROM     [Progress].[dbo].[ORDER_HEADERS]
 
GROUP BY [OH_LEDGER]
 
--HAVING   ([MaxDate]<Cast('2010-02-10' AS DateTime))
--HAVING   ([MaxDate]<#02/10/2010#)
 
ORDER BY [MaxDate] DESC
GO

Line #9 gives an error of :
Msg 207, Level 16, State 1, Line 9
Invalid column name 'MaxDate'.

Line #10 gives an error of :
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '#'.
My SQL experience is mainly in Access so I do struggle in areas where that differs from T-SQL. I'm getting there, but I cannot for the life of me, see why this is reacting as it is. It all seems fine to me. Clearly my eyes need some more experience.

If anyone can throw any light then I'd be grateful.

[NeoPa]

It suddenly occurred to me that a problem I thought was Access (Jet) specific, where aliases are not available to reference within the SQL, may be the cause of the non-recognition of [MaxDate] even though it seems to work fine in the ORDER BY clause. This turned out to be the cause of that problem (Line #9). I still have no idea why that would be the case for a HAVING clause but not for the ORDER BY clause.

I still have no idea why ANSI-92 standard date literal representation is causing a problem for line #10.

[ck9663]

You can't use the alias in any other part of the same SELECT statement. On the HAVING clause, use the entire expression that comprises the MaxDate alias... CAST(Max([OH_ENTERED_DATE]) AS DateTime)

Happy Coding!!!

~~ CK

[NeoPa]

ck9663: You can't use the alias in any other part of the same SELECT statement.

Hi CK. Thanks for posting.

I'm curious what you mean by the statement quoted. Are you referring to the SQL statement or the SELECT clause?

Sorry to sound picky, but I can't make it work either way I look at it. The alias is working in the ORDER BY clause of the SQL statement, but not in the HAVING clause. I would have thought that it would also work in the SELECT clause, but I haven't tested that (I'm posting from home at the moment). By the way, I did find that your suggested change worked (I tested it yesterday when I posted my second message, but didn't make my meaning very clear in that post).

Did the other part of the problem make any sense to you by the way?