>the interesting note here is when I changed the hire_date value to something other than 00:00:00 in the time by using getdate() function and right clicked
>'return all rows', I got date+time not only the date. Also Query Analyser will always show you the time part. So that could be by design.

Thanks for the observation, Bob.