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
3 | current_timestamp as [currenttimestamp], |
4 | getutcdate() as [getutcdate] |
6 | sysdatetime() as [sysdatetime], |
7 | sysutcdatetime() as [sysutcdatetime], |
8 | sysdatetimeoffset() as [sysdatetimeoffset] |
Here is the result in SSMS.

Formatting Date and Time
1 | DECLARE @DateTime DATETIME2 |
2 | SET @DateTime = sysdatetime() |
4 | PRINT FORMAT(@DateTime, 'yyyy/MM/dd' ) |
5 | PRINT FORMAT(@DateTime, 'yyyy' ) |
6 | PRINT FORMAT(@DateTime, 'hh' ) |
7 | PRINT FORMAT(@DateTime, 'HH' ) |
8 | PRINT FORMAT(@DateTime, 'hh:mm' ) |
9 | PRINT FORMAT(@DateTime, 'hh:mm:ss' ) |
10 | PRINT FORMAT(@DateTime, 'mm' ) |
11 | PRINT FORMAT(@DateTime, 'yy' ) |
12 | PRINT FORMAT(@DateTime, 'yyyy' ) |
13 | PRINT FORMAT(@DateTime, 'MM' ) |
14 | PRINT FORMAT(@DateTime, 'dd' ) |
15 | PRINT FORMAT(@DateTime, 'yyyy-MM-ddTHH:mm:ss.ssssZ' ) |
Here are the results of the above query as shown in SSMS.
1 | 2017-11-23 11:09:04.7147444 |
13 | 2017-11-23T11:09:04.04Z |
Below are examples of formatting.
1 | SET @DateTime = '2017-4-6 14:12:01.328' |
6 | PRINT DATEPART( hour ,@DateTime) |
7 | PRINT DATEPART( minute ,@DateTime) |
8 | PRINT DATEPART( second ,@DateTime) |
9 | PRINT DATEPART(millisecond,@DateTime) |