select convert(varchar, getdate(), 1) 03/10/09
select convert(varchar, getdate(), 3) 10/03/09
select convert(varchar, getdate(), 5) 10-03-09
select convert(varchar, getdate(), 6) 10 Mar 09
select convert(varchar, getdate(), 10) 03-10-09
select convert(varchar, getdate(), 11) 09/03/10
select convert(varchar, getdate(), 101) 03/10/2009
select convert(varchar, getdate(), 103) 10/03/2009
select convert(varchar, getdate(), 107) Mar 10, 2009
select convert(varchar, getdate(), 110) 03-10-2009
select convert(varchar, getdate(), 111) 2009/03/10
To drop the timestamp from the date and then replace timestamp with zeros use..
select GETDATE() -- 2009-03-10 15:31:56.340
select cast(convert (varchar(10), getdate(), 101) as datetime)TimeDate -- 2009-03-10 00:00:00.000
