Datetime2

Question

Post reply

Datetime2

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
December 22, 2015 at 8:56 pm

#74524

Comments posted to this topic are about the item Datetime2

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

HappyGeek SSCoach Points: 18726 More actions · Answer 1

Christmas must have come early, good question Steve, thanks.

...

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Umer Akram

SSC-Insane

Points: 22209

December 23, 2015 at 2:42 am

#1847662

nice and easy thanks for the question Steve

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

Thanks for that one. Though whenever a column is defined as datetime2 we always have used (7). Never much thought about it.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

Nice question. I still use the datetime data type, but appreciate the flexibility of having options.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 5

Prassad Dabbada V R

Ten Centuries

Points: 1251

December 23, 2015 at 7:57 am

#1847751

Very good question, thanks for shsring

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 7

TomThomson

SSC Guru

Points: 104773

December 24, 2015 at 7:07 am

#1847918

Nice question.

Tom

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 8

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 ... )

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 9

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."

😎

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 10

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.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 11

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 12

Too easy 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP