由SQL Server的数据导出时间转化问题引发的

问题描述

使用SQL Server 2012 附加一个数据库之后，想批修改数据中的时间，把时间日期提前，因为有很多表中有时间字段，在一个个表找，用sql更新，过于枯燥。想找一个一次性把所有表时时间都换成一样的方式。所以想到导出成sql脚本，然后用正则表达式查找替换，再重新建库。（当然情况是表比较多，但每个表中的数据量并不太多）

过程

先是参考SQLserver 2008将数据导出到Sql脚本文件的方法
发现只导出了表结构，未导出数据。
再找到了这篇SQL Server 2012 将数据导出为脚本详细图解 才搞定。
评价几句

• 没有记住上次向导的配置，每次导都要再配置一次，不方便。
• 高级配置里的选项那多么，却只分配那么小的框，要上下移，都做成向导页面了，就放一个高级配置的向导页就OK了嘛。
• 默认配置不合理。

新问题

数据是导出来了。
但发现插入时间的语句是这样的INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A44E00000000 AS DateTime))。
那CAST后面的长串16进制是什么鬼。
碰见反常的东西，是要好奇查查。
MSDN 里 CAST 和 CONVERT (Transact-SQL)，按图索冀 表达式（Transact-SQL) 然后就呵呵，断了。

• 查msdn时，总有这样的感觉，A页面说，详细请见B页面，然后B页面就是与A页面没太大联系的说明。
• 为什么要设计成这样，非要再转一次才能看到真的时间？

继续摸

如果时间紧，就不用在这里再探索那16个字符与时间的对应关系了。建个临时表，再导出需要调整的时间，再用导出的那个16进制数，替换那所有的数据就可以了。重放攻击的原理？

我想了解那个与时间的对应关系，先google了几把，为好选关键字，没找到。
于是我就自定义的一些数据，再导出来。比较数据，来看看到底是什么关系。
这是建表语句：

create table test(dt datetime);insert into test values('2015-03-01');insert into test values('2015-03-02');insert into test values('2015-03-03');insert into test values('2015-03-04 12:00:00.000');insert into test values('2015-03-04 12:00:00.001');insert into test values('2015-03-04 12:00:01.000');insert into test values('2015-03-04 12:01:00.000');insert into test values('2015-03-04 13:01:00.000');

导出的数据为：

INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A44E00000000 AS DateTime))INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A44F00000000 AS DateTime))INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A45000000000 AS DateTime))INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A45100000000 AS DateTime))INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A45100C5C100 AS DateTime))INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A45100C5C100 AS DateTime))INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A45100C5C22C AS DateTime))INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A45100C60750 AS DateTime))INSERT [dbo].[test] ([dt]) VALUES (CAST(0x0000A45100D68210 AS DateTime))

找规律：
日期加1时，前四个字节的数据就加1。猜想不会不是一个天数呢，先把前四个字节转成10进制42062，再除以365，再减现在的年份，1900？，验算下，从1900-01-01到2015-03-01的确是42062天。
unix时间戳是1970年开算，嘿嘿，它就非要不一样。
想看看1900与时间戳有什么关系。google一把，终于找到了一篇帖子SQL Server datetime和timestamp的区别

Microsoft SQL Server 用两个 4 字节的整数内部存储 datetime 数据类型的值。第一个 4 字节存储 base date（即 1900 年 1 月 1 日）之前或之后的天数。基础日期是系统参考日期。不允许早于 1753 年 1 月 1 日的 datetime 值。另外一个 4 字节存储以午夜后毫秒数所代表的每天的时间。
smalldatetime 数据类型存储日期和每天的时间，但精确度低于 datetime。SQL Server 将 smalldatetime 的值存储为两个 2 字节的整数。第一个 2 字节存储 1900 年 1 月 1 日后的天数。另外一个 2 字节存储午夜后的分钟数。日期范围从1900 年 1 月 1 日到 2079 年 6 月 6 日，精确到分钟。

到这里，问题算是解决了。

结果

这样一个“奇怪”的写法，是可能是为解决两个问题：

• 精度问题，要精确到毫秒，又要时间跨度。
• 时间区问题，显示的写时间，未指定时区，在不同时区执行会引发问题。

但还有三点问题

• 第四行和第五行，插入时，相差1ms，但导出时，数据是一样的？
• 现是东八区，取后四字节算出来的12点的时间是到3点的。奇怪？
• 如果是1900之前的时间，会出现什么情况呢？

等有空时再试试。

总结&反思

• 这样一个问题的确也折腾了我不少时间。是否值得？（本来可能不值的，但写了这篇总结后，是值得的）
• 找问题的方式和方向是不是错了？在试着CAST等关键字不行时，为什么没有“回溯”换成查找这个数据类型相关资料，而要自己蛮力的找规律？这是程序员的一分负面的执着。
• 一些看起来奇怪的事，可能是自己知识有限，未考虑到的情况，应抱着谦虚的心态，搞明白为什么，多交流。