December 22, 2015 at 8:56 pm
Comments posted to this topic are about the item Datetime2
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 23, 2015 at 12:53 am
Christmas must have come early, good question Steve, thanks.
...
December 23, 2015 at 2:42 am
nice and easy thanks for the question Steve
December 23, 2015 at 5:14 am
Thanks for that one. Though whenever a column is defined as datetime2 we always have used (7). Never much thought about it.
December 23, 2015 at 5:16 am
Nice question. I still use the datetime data type, but appreciate the flexibility of having options.
December 23, 2015 at 7:57 am
Very good question, thanks for shsring
December 23, 2015 at 8:24 am
Early Christmas gift... thanks Steve.
Shifting gears to one of my "favorite" rants...
Heh... if you use DATETIME2(3), you get approximately the same precision as DATETIME (3.3 milliseconds rounded to the nearest ms). For that, you get to save an awesome 1 byte and you lose the ability to do simple direct date math. You also gain the ability to make calendar mistakes because any datetime before 1753 used totally different calendars. It's really a shame they didn't spend a little more time when they create these "advanced" date/time datatypes.
--Jeff Moden
December 24, 2015 at 7:07 am
Nice question.
Tom
December 26, 2015 at 11:56 am
Nice and easy question. I was able to answer because I have some search about the meaning of the n parameter and the impact of the compression when it is enabled on a database.
Surely a stupid question , the date of this question was 2015/12/23 but it was not a Week-End question ( too often easy as we are tired... ). I know it is very bad humour but to-day I have struggled to create an .Net 6.0 application creating a big database and loading data from .csv files. The .sql script has only 5000 lines. The next step is to do the same thing but in SMO ( horror . I will have to use the 3.5 Framework ... )
December 26, 2015 at 12:34 pm
The explanation isn't correct, "This determines the storage for the type, but only in varying the storage from 6 to 8 digits of precision." should be "This determines the storage for the type, but only in varying the storage from 6 to 8 bytes depending on the precision used."
😎
December 27, 2015 at 1:13 am
Eirikur Eiriksson (12/26/2015)
The explanation isn't correct, "This determines the storage for the type, but only in varying the storage from 6 to 8 digits of precision." should be "This determines the storage for the type, but only in varying the storage from 6 to 8 bytes depending on the precision used."😎
+1 I missed it . Thanks Eirikur , I will do some more search about this morning.
December 27, 2015 at 1:32 am
patricklambin (12/27/2015)
Eirikur Eiriksson (12/26/2015)
The explanation isn't correct, "This determines the storage for the type, but only in varying the storage from 6 to 8 digits of precision." should be "This determines the storage for the type, but only in varying the storage from 6 to 8 bytes depending on the precision used."😎
+1 I missed it . Thanks Eirikur , I will do some more search about this morning.
Quick demonstration
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @D DATE = CONVERT(DATE,GETDATE(),0);
DECLARE @dt DATETIME = GETDATE();
DECLARE @DT20 DATETIME2(0) = SYSDATETIME();
DECLARE @DT21 DATETIME2(1) = SYSDATETIME();
DECLARE @DT22 DATETIME2(2) = SYSDATETIME();
DECLARE @DT23 DATETIME2(3) = SYSDATETIME();
DECLARE @DT24 DATETIME2(4) = SYSDATETIME();
DECLARE @DT25 DATETIME2(5) = SYSDATETIME();
DECLARE @DT26 DATETIME2(6) = SYSDATETIME();
DECLARE @DT27 DATETIME2(7) = SYSDATETIME();
SELECT 'DATE' AS DATA_TYPE
,DATALENGTH(@D) AS BYTE_USED
,0 AS USED_PRECISION
,@D AS USED_VALUE
UNION ALL
SELECT 'DATETIME' AS DATA_TYPE
,DATALENGTH(@DT) AS BYTE_USED
,3 AS USED_PRECISION
,@DT AS USED_VALUE
UNION ALL
SELECT 'DATETIME2(0)' AS DATA_TYPE
,DATALENGTH(@DT20) AS BYTE_USED
,0 AS USED_PRECISION
,@DT20 AS USED_VALUE
UNION ALL
SELECT 'DATETIME2(1)' AS DATA_TYPE
,DATALENGTH(@DT21) AS BYTE_USED
,1 AS USED_PRECISION
,@DT21 AS USED_VALUE
UNION ALL
SELECT 'DATETIME2(2)' AS DATA_TYPE
,DATALENGTH(@DT22) AS BYTE_USED
,2 AS USED_PRECISION
,@DT22 AS USED_VALUE
UNION ALL
SELECT 'DATETIME2(3)' AS DATA_TYPE
,DATALENGTH(@DT23) AS BYTE_USED
,3 AS USED_PRECISION
,@DT23 AS USED_VALUE
UNION ALL
SELECT 'DATETIME2(4)' AS DATA_TYPE
,DATALENGTH(@DT24) AS BYTE_USED
,4 AS USED_PRECISION
,@DT24 AS USED_VALUE
UNION ALL
SELECT 'DATETIME2(5)' AS DATA_TYPE
,DATALENGTH(@DT25) AS BYTE_USED
,5 AS USED_PRECISION
,@DT25 AS USED_VALUE
UNION ALL
SELECT 'DATETIME2(6)' AS DATA_TYPE
,DATALENGTH(@DT26) AS BYTE_USED
,6 AS USED_PRECISION
,@DT26 AS USED_VALUE
UNION ALL
SELECT 'DATETIME2(7)' AS DATA_TYPE
,DATALENGTH(@DT27) AS BYTE_USED
,7 AS USED_PRECISION
,@DT27 AS USED_VALUE
;
Output
DATA_TYPE BYTE_USED USED_PRECISION USED_VALUE
------------ ----------- -------------- ---------------------------
DATE 3 0 2015-12-27 00:00:00.0000000
DATETIME 8 3 2015-12-27 08:33:06.9330000
DATETIME2(0) 6 0 2015-12-27 08:33:07.0000000
DATETIME2(1) 6 1 2015-12-27 08:33:06.9000000
DATETIME2(2) 6 2 2015-12-27 08:33:06.9300000
DATETIME2(3) 7 3 2015-12-27 08:33:06.9350000
DATETIME2(4) 7 4 2015-12-27 08:33:06.9346000
DATETIME2(5) 8 5 2015-12-27 08:33:06.9345900
DATETIME2(6) 8 6 2015-12-27 08:33:06.9345870
DATETIME2(7) 8 7 2015-12-27 08:33:06.9345870
January 4, 2016 at 4:36 am
Too easy 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply