>>>>>ADS has a Time datatype, which is internally stored as the number of milliseconds since midnight in a 4-byte integer.
>>>>
>>>>OK the same query, but you should find the right functions for converting:
>>>>
>>>>SELECT YourTable.*;
>>>> FROM YourTable;
>>>>INNER JOIN (SELECT Sh, MAX(ConvertDateToISO(DateSaved)+' '+ConvertTimeToString(TimeSaved)) AS DateTimeSaved;
>>>> FROM YourTable;
>>>> GROUP BY Sh) Tbl1;
>>>>ON YourTable.Sh = Tbl1.Sh AND;
>>>> ConvertDateToISO(YourTable.DateSaved)+' '+ConvertTimeToString(YourTable.TimeSaved) = Tbl1.DateTimeSaved
>>>>
>>>>
>>>>Where ConvertDateToISO() should convert your Date to string with folwong format "YYYYMMDD" and
>>>> ConvertTimeToString() should convert Time to string no matter what format it is, just make sure that 12:01 PM is bigger than 12:01 AM it could be:
>>>>"18:20", "1820", "66000" (that is 18*3600 + 20*60)
>>>
>>>Hi Borislav,
>>>
>>>Please check my suggestion. It may be slower, but no conversions are needed. First we filter the maximum date, then the maximum time for that date.
>>
>>
>>I currently have this statement
>>
>>select TOP 1 *
>>from CardDetail
>>WHERE CardSN = 4162078351
>>order by DateSaved DESC , TimeSaved DESC
>>
>>This statement is what I need but that is only for one CardSn, I need it for all CardSn in the table
>
>
>Try Naomi's suggestion, it should work w/o any conversion. I just didn't read it right.
>That should give you want you want. Max record based on both date and time fields.
>Just hope ADS support such queries.
It does not appear that ADS supports this query.
I am thinking about reading the ADS table into .NET and doing the processing in .NET instead.
Semper ubi sub ubi.