Friday, June 08, 2012

SQL Server Date/Time Byte Counts

Here is a table showing how many bytes each date/time datatype requires. The script to generate the table is after the table.

TypeByteCtExample Precision
date32012-06-08
smalldatetime42012-06-08 16:27:00.000
datetime82012-06-08 16:27:12.187
time(0)316:27:12
time(1)316:27:12.2
time(2)316:27:12.19
time(3)416:27:12.188
time(4)416:27:12.1875
time(5)516:27:12.18751
time(6)516:27:12.187506
time(7)516:27:12.1875059
datetime2(0)62012-06-08 16:27:12
datetime2(1)62012-06-08 16:27:12.2
datetime2(2)62012-06-08 16:27:12.19
datetime2(3)72012-06-08 16:27:12.188
datetime2(4)72012-06-08 16:27:12.1875
datetime2(5)82012-06-08 16:27:12.18751
datetime2(6)82012-06-08 16:27:12.187506
datetime2(7)82012-06-08 16:27:12.1875059
datetimeoffset(0)82012-06-08 16:27:12 -07:00
datetimeoffset(1)82012-06-08 16:27:12.2 -07:00
datetimeoffset(2)82012-06-08 16:27:12.19 -07:00
datetimeoffset(3)92012-06-08 16:27:12.188 -07:00
datetimeoffset(4)92012-06-08 16:27:12.1875 -07:00
datetimeoffset(5)102012-06-08 16:27:12.18751 -07:00
datetimeoffset(6)102012-06-08 16:27:12.187506 -07:00
datetimeoffset(7)102012-06-08 16:27:12.1875059 -07:00

Query

SET NOCOUNT ON;DECLARE @now    datetime2(7)        = SYSDATETIME();DECLARE @nowU   datetime2(7)        = SYSUTCDATETIME();DECLARE @nowO   datetimeoffset(7)   = SYSDATETIMEOFFSET();DECLARE @d      date                = @now;DECLARE @s      smalldatetime       = @now;DECLARE @dt     DATETIME            = @now;DECLARE @t0     time(0)             = @now;DECLARE @t1     time(1)             = @now;DECLARE @t2     time(2)             = @now;DECLARE @t3     time(3)             = @now;DECLARE @t4     time(4)             = @now;DECLARE @t5     time(5)             = @now;DECLARE @t6     time(6)             = @now;DECLARE @t7     time(7)             = @now;DECLARE @dt0    datetime2(0)        = @now;DECLARE @dt1    datetime2(1)        = @now;DECLARE @dt2    datetime2(2)        = @now;DECLARE @dt3    datetime2(3)        = @now;DECLARE @dt4    datetime2(4)        = @now;DECLARE @dt5    datetime2(5)        = @now;DECLARE @dt6    datetime2(6)        = @now;DECLARE @dt7    datetime2(7)        = @now;DECLARE @dto0   datetimeoffset(0)   = @nowO;DECLARE @dto1   datetimeoffset(1)   = @nowO;DECLARE @dto2   datetimeoffset(2)   = @nowO;DECLARE @dto3   datetimeoffset(3)   = @nowO;DECLARE @dto4   datetimeoffset(4)   = @nowO;DECLARE @dto5   datetimeoffset(5)   = @nowO;DECLARE @dto6   datetimeoffset(6)   = @nowO;DECLARE @dto7   datetimeoffset(7)   = @nowO;
SELECT 'date'           AS 'Type',   DATALENGTH(@d)  AS 'ByteCt', CONVERT(VARCHAR(100), @d, 121) AS 'Example Precision'UNION ALLSELECT 'smalldatetime',             DATALENGTH(@s),                 CONVERT(VARCHAR(100), @s, 121)UNION ALLSELECT 'datetime',                  DATALENGTH(@dt),                CONVERT(VARCHAR(100), @dt, 121)UNION ALLSELECT 'time(0)',                   DATALENGTH(@t0),                CONVERT(VARCHAR(100), @t0, 121)UNION ALLSELECT 'time(1)',                   DATALENGTH(@t1),                CONVERT(VARCHAR(100), @t1, 121)UNION ALLSELECT 'time(2)',                   DATALENGTH(@t2),                CONVERT(VARCHAR(100), @t2, 121)UNION ALLSELECT 'time(3)',                   DATALENGTH(@t3),                CONVERT(VARCHAR(100), @t3, 121)UNION ALLSELECT 'time(4)',                   DATALENGTH(@t4),                CONVERT(VARCHAR(100), @t4, 121)UNION ALLSELECT 'time(5)',                   DATALENGTH(@t5),                CONVERT(VARCHAR(100), @t5, 121)UNION ALLSELECT 'time(6)',                   DATALENGTH(@t6),                CONVERT(VARCHAR(100), @t6, 121)UNION ALLSELECT 'time(7)',                   DATALENGTH(@t7),                CONVERT(VARCHAR(100), @t7, 121)UNION ALLSELECT 'datetime2(0)',              DATALENGTH(@dt0),               CONVERT(VARCHAR(100), @dt0, 121)UNION ALLSELECT 'datetime2(1)',              DATALENGTH(@dt1),               CONVERT(VARCHAR(100), @dt1, 121)UNION ALLSELECT 'datetime2(2)',              DATALENGTH(@dt2),               CONVERT(VARCHAR(100), @dt2, 121)UNION ALLSELECT 'datetime2(3)',              DATALENGTH(@dt3),               CONVERT(VARCHAR(100), @dt3, 121)UNION ALLSELECT 'datetime2(4)',              DATALENGTH(@dt4),               CONVERT(VARCHAR(100), @dt4, 121)UNION ALLSELECT 'datetime2(5)',              DATALENGTH(@dt5),               CONVERT(VARCHAR(100), @dt5, 121)UNION ALLSELECT 'datetime2(6)',              DATALENGTH(@dt6),               CONVERT(VARCHAR(100), @dt6, 121)UNION ALLSELECT 'datetime2(7)',              DATALENGTH(@dt7),               CONVERT(VARCHAR(100), @dt7, 121)UNION ALLSELECT 'datetimeoffset(0)',         DATALENGTH(@dto0),              CONVERT(VARCHAR(100), @dto0, 121)UNION ALLSELECT 'datetimeoffset(1)',         DATALENGTH(@dto1),              CONVERT(VARCHAR(100), @dto1, 121)UNION ALLSELECT 'datetimeoffset(2)',         DATALENGTH(@dto2),              CONVERT(VARCHAR(100), @dto2, 121)UNION ALLSELECT 'datetimeoffset(3)',         DATALENGTH(@dto3),              CONVERT(VARCHAR(100), @dto3, 121)UNION ALLSELECT 'datetimeoffset(4)',         DATALENGTH(@dto4),              CONVERT(VARCHAR(100), @dto4, 121)UNION ALLSELECT 'datetimeoffset(5)',         DATALENGTH(@dto5),              CONVERT(VARCHAR(100), @dto5, 121)UNION ALLSELECT 'datetimeoffset(6)',         DATALENGTH(@dto6),              CONVERT(VARCHAR(100), @dto6, 121)UNION ALLSELECT 'datetimeoffset(7)',         DATALENGTH(@dto7),              CONVERT(VARCHAR(100), @dto7, 121);

No comments:

Post a Comment