SQL Server Datetime


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

1select
2getdate() as [getdate],
3current_timestamp as [currenttimestamp],  -- note the lack of parentheses
4getutcdate() as [getutcdate]
5select
6sysdatetime() as [sysdatetime],
7sysutcdatetime() as [sysutcdatetime],
8sysdatetimeoffset() as [sysdatetimeoffset]

Here is the result in SSMS.

Formatting Date and Time

1DECLARE @DateTime DATETIME2
2SET @DateTime = sysdatetime()
3PRINT @DateTime
4PRINT FORMAT(@DateTime, 'yyyy/MM/dd'-- Month must be capitalized as MM, not mm
5PRINT FORMAT(@DateTime, 'yyyy')
6PRINT FORMAT(@DateTime, 'hh'-- 12 hour
7PRINT FORMAT(@DateTime, 'HH'-- 24 hour
8PRINT FORMAT(@DateTime, 'hh:mm')
9PRINT FORMAT(@DateTime, 'hh:mm:ss')
10PRINT FORMAT(@DateTime, 'mm'-- minute
11PRINT FORMAT(@DateTime, 'yy')
12PRINT FORMAT(@DateTime, 'yyyy')
13PRINT FORMAT(@DateTime, 'MM'-- month
14PRINT FORMAT(@DateTime, 'dd')
15PRINT FORMAT(@DateTime, 'yyyy-MM-ddTHH:mm:ss.ssssZ'-- cannot print precision. why?

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

12017-11-23 11:09:04.7147444
22017/11/23
32017
411
511
611:09
711:09:04
809
917
102017
1111
1223
132017-11-23T11:09:04.04Z

Below are examples of formatting.

1SET @DateTime = '2017-4-6 14:12:01.328'
2PRINT @DateTime
3PRINT YEAR(@DateTime)
4PRINT MONTH(@DateTime)
5PRINT DAY(@DateTime)
6PRINT DATEPART(hour,@DateTime)
7PRINT DATEPART(minute,@DateTime)
8PRINT DATEPART(second,@DateTime)
9PRINT DATEPART(millisecond,@DateTime)