SQL server date functions

To find current date and time : select SYSDATETIME();

To display only date : select convert(date,SYSDATETIME());

To display only time : select convert(time,SYSDATETIME());

Here is the query to display all this above.

select SYSDATETIME() as Timestamp,convert(date,SYSDATETIME()) as Date,CONVERT(time,SYSDATETIME()) as Time;