Unanswered: "Datetime field overflow" w/ MAX in subselect?
Hey guys---hoping you can help with a problematic query. When I run it, I get an error, copied here from the ColdFusion application of which it's a part: "ODBC Error Code = 22008 (Datetime field overflow) [MERANT][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type."
And here's the query:
WHERE (StoryID IN
(SELECT MAX(PublishTime) AS PublishTime
WHERE (Fixture_Code IN ('test1', 'test2'))
GROUP BY Fixture_Code))
The subselect works great when run by itself; it's only when it's a subselect that I get type errors. PublishTime is a smalldatetime column. The above query actually runs without an error if my values for WHERE IN are values that don't exist in the the Fixture_Code column anywhere---obviously I get back an empty set, but no error.
In case it's not clear, I'm trying in the subselect to pick the single most recent record for each of a list of fixture codes, so that I can then go back and get full information on each of those stories. If someone can tell me a better way to, essentially, do a query where the result set contains only one row (the most recent) for each of my "fixture codes", I'm open to that too.
Although, when I tweak the query to specify only the list of fixtures I want, plus bring in another table join to get a fixture name in addition to the fixture code, I get one row that's duplicated in the results.
SELECT s.*, Fixture_Name
FROM Stories s INNER JOIN
(SELECT MAX(PublishTime) AS PublishTime, Fixture_Code
GROUP BY Fixture_Code) x
JOIN FixtureCodes ON x.Fixture_Code = FixtureCodes.Fixture_ID
ON s.Fixture_Code = x.Fixture_Code AND s.PublishTime = x.PublishTime AND s.Fixture_Code IN ('test1','test2','test3','test4','test5','test6',' test7','test8')
Very easily taken care of with DISTINCT but if anyone cared to further edify me with the reason that's happening, I'd be grateful :) Thanks again to all.