sql server 中日期时间的内部表示是什么? [英] What is the internal representation of datetime in sql server?

问题描述

存储在 SQL Server 中的日期时间值的基础数据结构是什么(2000 和 2005，如果不同)?即到字节表示?

What is the underlying datastructure of datetime values stored in SQL Server (2000 and 2005 if different)? Ie down to the byte representation?

大概您在选择日期时间列时获得的默认表示是特定于文化的值/可能会更改.也就是说，一些我们看不到的底层结构被格式化为 YYYY-MM-DD HH:MM:SS.mmm.

Presumably the default representation you get when you select a datetime column is a culture specific value / subject to change. That is, some underlying structure that we don't see is getting formatted to YYYY-MM-DD HH:MM:SS.mmm.

我问的原因是我的部门普遍认为它存储在内存中的字面意思是 YYYY-MM-DD HH:MM:SS.mmm，但我确定事实并非如此.

Reason I ask is that there's a generally held view in my department that it's stored in memory literally as YYYY-MM-DD HH:MM:SS.mmm but I'm sure this isn't the case.

推荐答案

It's 存储作为一个 8 字节的字段，范围从 1753-01-01 到 9999-12-31，精确到 0.00333 秒.

It's stored as an 8 byte field, capable of a range from 1753-01-01 through 9999-12-31, accurate to 0.00333 seconds.

细节应该是不透明的，但大多数资源(1), 我在网上找到的 (2) 声明如下:

The details are supposedly opaque, but most resources (1), (2) that I've found on the web state the following:

前 4 个字节存储自 SQL Server 纪元(1900 年 1 月 1 日)以来的天数，后 4 个字节存储午夜之后的滴答数，其中滴答"为 3.3 毫秒.

The first 4 bytes store the number of days since SQL Server's epoch (1st Jan 1900) and that the second 4 bytes stores the number of ticks after midnight, where a "tick" is 3.3 milliseconds.

前四个字节是有符号的(可以是正数或负数)，这就解释了为什么可以表示早于纪元的日期.

The first four bytes are signed (can be positive or negative), which explains why dates earlier than the epoch can be represented.

这篇关于sql server 中日期时间的内部表示是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！