The following gives you the most recent record per id
SELECT * FROM
(
SELECT a.* ,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date DESC) rn
)a
WHERE a.rn =1;
I'm not sure what you mean by complications when querying column of type DATE. 2010-01-01 is the same as midnight of Jan 1, 2010.