Wednesday 30 November 2016

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



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
*/ 


No comments:

Post a Comment