in MS SQL there is a function DATEDIFF(datepart, startdate, enddate), which returns an integer value of the time difference betwen two different datatime values. datepart is one of the following: year, quarter, month, dayofyear, day, hour, minute, second or millisecond.
and you can use t1.QuizDate = (select max(datediff(hour,'1910-01-01',QuizDate)) from ....) HTH Hua On Dec 4, 2007 9:25 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > i have the following sql: > > SELECT MAX(QuizDate) as QuizDate, QuizID, > QuizPercent = (SELECT QuizPercent FROM test_module as t1 > WHERE t1.Customer_ID = 21 > AND t1.QuizID = test_module.QuizID > AND t1.QuizDate = MAX(test_module.QuizDate) > > FROM test_module > WHERE Customer_ID = 21 > GROUP BY QuizID > > it works fine until the QuizPercent subquery returns more than one record > when the user has, for example, multiple 100% scores. so i thought of > isolating and looking for max date and max time since the same scores would > be done at a different time by doing the following: > > SELECT MAX(QuizDate) as QuizDate, QuizID, > QuizPercent = (SELECT QuizPercent FROM test_module as t1 > WHERE t1.Customer_ID = 21 > AND t1.QuizID = test_module.QuizID > AND t1.QuizDate = MAX(Day(test_module.QuizDate)) > AND t1.QuizDate = MAX(Time(test_module.QuizDate)) > > FROM test_module > WHERE Customer_ID = 21 > GROUP BY QuizID > > but in SQL there is no > > MAX(Day(test_module.QuizDate)) > AND t1.QuizDate = MAX(Time(test_module.QuizDate)) > > and it generates an error. > > suggestions? > > sebastian > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294172 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
