opps, should be datediff(hour,'1910-01-01',t1.QuizDate) = (select max(datediff(hour,'1910-01-01',QuizDate)) from ....) ( notice hour is used as an example ) .
On Dec 4, 2007 12:24 PM, hua waveland <[EMAIL PROTECTED]> wrote: > 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 > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294180 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
