The topic of date and time in SQL Server requires more attention than any of the other data types in SQL Server. Microsoft has a webpage that has critical information on these data types called Date and Time Data Types and Functions (Transact SQL).

There are six date and time data types. DATETIME and SMALLDATETIME have been around in SQL Server for the longest time. When SQL Server 2008 arrived, they added DATE, TIME, DATETIME2 and DATETIMEOFFSET.

When choosing a type, use the smallest one that covers your current needs and will cover your future needs. Smaller types use less storage. If you need a separation between date and time you should use the date and time data types respectively.

Date and Time Functions

select 
getdate() as [getdate],
current_timestamp as [currenttimestamp],  -- note the lack of parentheses
getutcdate() as [getutcdate]
select
sysdatetime() as [sysdatetime],
sysutcdatetime() as [sysutcdatetime],
sysdatetimeoffset() as [sysdatetimeoffset]

Here is the result in SSMS.

Formatting Date and Time

DECLARE @DateTime DATETIME2 
SET @DateTime = sysdatetime()
PRINT @DateTime
PRINT FORMAT(@DateTime, 'yyyy/MM/dd')  -- Month must be capitalized as MM, not mm
PRINT FORMAT(@DateTime, 'yyyy')
PRINT FORMAT(@DateTime, 'hh')  -- 12 hour
PRINT FORMAT(@DateTime, 'HH')  -- 24 hour
PRINT FORMAT(@DateTime, 'hh:mm')
PRINT FORMAT(@DateTime, 'hh:mm:ss')
PRINT FORMAT(@DateTime, 'mm')  -- minute
PRINT FORMAT(@DateTime, 'yy')
PRINT FORMAT(@DateTime, 'yyyy')
PRINT FORMAT(@DateTime, 'MM')  -- month
PRINT FORMAT(@DateTime, 'dd')
PRINT FORMAT(@DateTime, 'yyyy-MM-ddTHH:mm:ss.ssssZ')  -- cannot print precision. why?

Here are the results of the above query as shown in SSMS.

2017-11-23 11:09:04.7147444
2017/11/23
2017
11
11
11:09
11:09:04
09
17
2017
11
23
2017-11-23T11:09:04.04Z

Below are examples of formatting.

SET @DateTime = '2017-4-6 14:12:01.328'
PRINT @DateTime
PRINT YEAR(@DateTime)
PRINT MONTH(@DateTime)
PRINT DAY(@DateTime)
PRINT DATEPART(hour,@DateTime)
PRINT DATEPART(minute,@DateTime)
PRINT DATEPART(second,@DateTime)
PRINT DATEPART(millisecond,@DateTime)