DATETIMEOFFSET, DATE, TIME, SMALLDATETIME, DATETIME SYSUTCDATETIME and SYSUTCDATE

Differences on DATETIMEOFFSET, DATE, TIME, SMALLDATETIME, DATETIME SYSUTCDATETIME and SYSUTCDATE.

	DATETIMEOFFSET
Syntax	DATETIMEOFFSET [ (fractional seconds precision) ]
Usage	DECLARE @MyDatetimeoffset datetimeoffset(7)
	CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
Default string literal formats (used for down-level client)	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
	For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
Date range	0001-01-01 through 9999-12-31.
	January 1, 1 CE through December 31, 9999 CE.
Time range	00:00:00 through 23:59:59.9999999. (fractional seconds are not supported in Informatica)
Time zone offset range	-14:00 through +14:00 (the time zone offset is ignored in Informatica)
Element ranges	YYYY is four digits, ranging from 0001 through 9999, that represent a year.
	MM is two digits, ranging from 01 to 12, that represent a month in the specified year.
	DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.
	hh is two digits, ranging from 00 to 23, that represent the hour.
	mm is two digits, ranging from 00 to 59, that represent the minute.
	ss is two digits, ranging from 00 to 59, that represent the second.
	n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. Fractional seconds are not supported in Informatica.
	hh is two digits that range from -14 to +14. The time zone offset is ignored in Informatica.
	mm is two digits that range from 00 to 59. The time zone offset is ignored in Informatica.
Character length	26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
Precision, scale	See the table below.
Storage size	10 bytes, fixed is the default with the default of 100ns fractional second precision.
Accuracy	100 nanoseconds.
Default value	1900-01-01 00:00:00 00:00.
Calendar	Gregorian
User-defined fractional second precision	Yes
Time zone offset aware and preservation	Yes
Daylight saving aware	No
DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10.1236 +01:0';  DECLARE @time time(3) = @datetimeoffset;  SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';  @datetimeoffset                    time ---------------------------------- ---------------- 2025-12-10 12:32:10.1236 +01:00    12:32:10.124 DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';  DECLARE @date date= @datetimeoffset;  SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';  @datetimeoffset                    date ---------------------------------- ---------- 2025-12-10 12:32:10.0000 +01:00    2025-12-10 DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';  DECLARE @datetimeoffset1 datetimeoffset= @datetimeoffset;  SELECT @datetimeoffset AS '@datetimeoffset ', @datetimeoffset1 AS '@datetimeoffset1';  @datetimeoffset                    @datetimeoffset1 ---------------------------------- ---------------------------------- 2025-12-10 12:32:10.0000 +01:00    2025-12-10 12:32:10.0000000 +01:00 DECLARE @date date= getdate();  DECLARE @datetimoffset datetimeoffset = @date;  SELECT @date AS '@date ', @datetimoffset AS '@datetimeoffset';  @date      @datetimeoffset ---------- ---------------------------------- 2016-11-25 2016-11-25 00:00:00.0000000 +00:00 SELECT        CAST('2007-05-08 12:35:29.1234567 +12:15' AS time(3)) AS 'time3'     ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS time) AS 'time'     ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS time(7)) AS 'time7'       ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS date) AS 'date'       ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 'smalldatetime'       ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'       ,CAST('2007-05-08 12:35:29.1234567+12:15' AS datetime2(7)) AS 'datetime2'      ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'      ,CAST('2007-05-08 12:35:29.1234567+12:15' AS datetimeoffset(7)) AS 'datetimeoffset IS08601';  Data type Output Time (3) 12:35:29.123 Time / Time(7) 12:35:29.1234567 – consider default like time(7) if not specified the time parameter. Date 2007-05-08 Smalldatetime 2007-05-08 12:35:00 Datetime  2007-05-08 12:35:29.123 datetime2 2007-05-08 12:35:29.12 Datetimeoffset 2007-05-08 12:35:29.1234567 +12:15 datetimeoffset IS08601 2007-05-08 12:35:29.1234567 +12:15 Refer for more details https://msdn.microsoft.com/en-us/library/bb630289.aspx A. Get the Current System Date and Time SELECT SYSDATETIME()      ,SYSDATETIMEOFFSET()      ,SYSUTCDATETIME()      ,CURRENT_TIMESTAMP      ,GETDATE()      ,GETUTCDATE(); /* Returned:  SYSDATETIME()                 2007-05-08 13:10:02.0474381  SYSDATETIMEOFFSET()   2007-05-08 13:10:02.0474381 -07:00  SYSUTCDATETIME()          2007-05-08  07:50:02.0474381 in INDIA subtract 5:30 hrs from current time   CURRENT_TIMESTAMP     2007-05-08 13:10:02.047  GETDATE()                          2007-05-08 13:10:02.047  GETUTCDATE()                   2007-05-08 07:50:02.047   in INDIA subtract 5:30 hrs from current time B. Get the Current System Date SELECT CONVERT (date, SYSDATETIME())      ,CONVERT (date, SYSDATETIMEOFFSET())      ,CONVERT (date, SYSUTCDATETIME())      ,CONVERT (date, CURRENT_TIMESTAMP)      ,CONVERT (date, GETDATE())      ,CONVERT (date, GETUTCDATE());      /* Returned   SYSDATETIME()                2007-05-03  SYSDATETIMEOFFSET()  2007-05-03  SYSUTCDATETIME()         2007-05-03  in INDIA subtract 5:30 hrs from current time CURRENT_TIMESTAMP   2007-05-03  GETDATE()                        2007-05-03  GETUTCDATE()                 2007-05-03   in INDIA subtract 5:30 hrs from current time */  C. Get the Current System Time SELECT CONVERT (time, SYSDATETIME())      ,CONVERT (time, SYSDATETIMEOFFSET())      ,CONVERT (time, SYSUTCDATETIME())         ,CONVERT (time, CURRENT_TIMESTAMP)      ,CONVERT (time, GETDATE())      ,CONVERT (time, GETUTCDATE());    /* Returned  SYSDATETIME()                13:18:45.3490361  SYSDATETIMEOFFSET() 13:18:45.3490361  SYSUTCDATETIME()        07:48:45.3490361  in INDIA subtract 5:30 hrs from current time CURRENT_TIMESTAMP  13:18:45.3470000  GETDATE()                        13:18:45.3470000  GETUTCDATE()                 07:48:45.3470000 in INDIA subtract 5:30 hrs from current time */

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF