In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits
e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int
yeah, that works, I am pretty sure the synax is right, and I can execute the statement without any errors, if I leave out the max_date>20040721 part.
BFCPN1.EndBook AS max_date
FROM BFCPN1 where
ORDER BY max_date
gives the same error. My problem is that I need to reference the max_date column in the where clause, and because I am replacing substrings and converting it to an int I have to assign it a name, and sql server won't allow me reference it in the where clause. It will allow me to reference it in the order by part.
The bits missing from the statement are just lists of other columns I need to select, and a group by clause. Left them out for clarity. The tables being called are a big list of varchars, which unfortunately can't be changed. (they are loaded each night from a unix flat file.