/*============================================================================
  File:     DateDatatypes.sql

  Summary:  Various examples of datetime data, their storage size and the 
			correct functions to use with better precision.
  
  SQL Server Version: SQL Server 2008+
------------------------------------------------------------------------------
  Written by Kimberly L. Tripp, SYSolutions, Inc.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- If you want to see the current session settings:
DBCC USEROPTIONS
go

-- If you want to see ALL session settings of those connected to the server:
SELECT * FROM sys.dm_exec_sessions
go  
	-- NOTE (BOL): If the user has VIEW SERVER STATE permission on the server, 
	-- the user will see all executing sessions on the instance of SQL Server; 
	-- otherwise, the user will see only the current session.

-- However, regardless of how the DATEFORMAT session setting is set - 
-- the ANSI/ISO dateformat YYYYMMDD *ALWAYS* works and is never
-- impacted by the dateformat setting.

-- But, it is impacted by the lack of precision that datetime/smalldatetime have:
SELECT CONVERT(datetime, '20091118 23:59:59.999')
SELECT CONVERT(datetime, '20091118 23:59:59.998')
SELECT CONVERT(datetime, '20091118 23:59:59.997')

-- A better choice is datetime2 - but, GETDATE() is datetime...
SELECT (CONVERT(datetime2(7), getdate()))
SELECT (CONVERT(datetime2(6), getdate()))
SELECT (CONVERT(datetime2(5), getdate()))

-- If you change your data type, be sure to also change the function calls:
SELECT (CONVERT(datetime2(7), sysdatetime()))
SELECT (CONVERT(datetime2(6), sysdatetime()))
SELECT (CONVERT(datetime2(5), sysdatetime()))

SELECT (CONVERT(datetime2(4), sysdatetime()))
SELECT (CONVERT(datetime2(3), sysdatetime()))

SELECT (CONVERT(datetime2(2), sysdatetime()))
SELECT (CONVERT(datetime2(1), sysdatetime()))
SELECT (CONVERT(datetime2(0), sysdatetime()))

-- And, how large are these new columns:
SELECT DATALENGTH(CONVERT(datetime2(7), sysdatetime()))
SELECT DATALENGTH(CONVERT(datetime2(6), sysdatetime()))
SELECT DATALENGTH(CONVERT(datetime2(5), sysdatetime()))

SELECT DATALENGTH(CONVERT(datetime2(4), sysdatetime()))
SELECT DATALENGTH(CONVERT(datetime2(3), sysdatetime()))

SELECT DATALENGTH(CONVERT(datetime2(2), sysdatetime()))
SELECT DATALENGTH(CONVERT(datetime2(1), sysdatetime()))
SELECT DATALENGTH(CONVERT(datetime2(0), sysdatetime()))

-- Finally, how do you use the offset?
SELECT CONVERT(datetimeoffset, SYSDATETIMEOFFSET(), 127) 
	-- this is the local time with the UTC offset

SELECT CONVERT(datetimeoffset, SYSUTCDATETIME(), 127)
	-- this is the UTC time






