[Saloni Upadhyay]

select convert(varchar,Datecolumn,101)
where
101 ->mm/dd/yyyy
108 ->hh:mm:ss
103 ->dd/mm/yyyy

Function for getting current date in sql --> getdate()