dror, are you going to ask us every time your query does not work the same way in sql server as it used to in access?
i think it's time you started looking these things up yourself in the sql server manual
here's one page that will help you when the time comes to display a datetime value in a particular format --
as for this specific "DateValue" problem, if you want rows of the table for a given date, you should select a range of datetime values like this --
where table_name.required_date >= getdate()
and table_name.required_date < dateadd(day,1,getdate())