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)