datetime类型分析

feilniu 2010-08-26 01:07:33
加精
日期时间类型的数据存储方式和可用值范围、相关的计算、比较、显示（转换为指定格式的字符串）都比较复杂，还涉及一组日期时间函数。常看到有新手困扰于相关问题，特整理下帖。


datetime类型

SQLServer中的datetime是8个字节，前4个字节表示从19000101开始的天数，后4个字节表示从00:00:00开始的10/3毫秒间隔的个数（最多精确到3又1/3毫秒）。
smalldatetime是4个字节，前2个字节表示天数，后2个字节表示时间，但日期范围和时间精度都比datetime低很多，详见联机丛书。

把datetime类型转成decimal，则前4个字节转为整数部分（天数），后4个字节转成小数部分（间隔个数比例）。
把datetime类型转成int，相当于对转成的decimal四舍五入取整。
如果需要按指定格式显示，则用CONVERT([n][var]char(x),datetime-type,style)的方式将日期转为字符串。各种可用的style和转换效果详见联机丛书。

相反方向的转换规则相同。

示例：


DECLARE @dt datetime

SET @dt = GETDATE()

SELECT @dt,

    CAST(@dt AS binary(8)), --实际物理存储格式

    CAST(@dt AS int),                        --datetime转为int是四舍五入

    CAST(CAST(@dt AS decimal(18,9)) AS int), --decimal转为int是舍去小数

    CAST(@dt AS decimal(18,9)),                        --datetime转为decimal

    CAST(SUBSTRING(CAST(@dt AS binary(8)),1,4) AS int) --分开日期和时间部分，分析datetime转为decimal的规则

        + CAST(SUBSTRING(CAST(@dt AS binary(8)),5,8) AS int) * 1.0 / (24 * 60 * 60 * 300) --一秒中有300个10/3毫秒的间隔


T-SQL不支持datetime类型的常量，通常是以指定格式字符串的方式表示，如果需要指定为datetime类型，则可以用CONVERT或CAST进行显式转换：


SELECT sdt,

    dt1 = CAST(sdt AS datetime),  --显式转换

    dt2 = DATEADD(day,0,std)      --隐式转换

FROM (

    SELECT sdt = '' UNION --CAST('' AS datetime) == CAST(0 AS datetime)

    SELECT sdt = '01:02' UNION

    SELECT sdt = '01:02:03:004' UNION

    SELECT sdt = '20100521' UNION

    SELECT sdt = '2010-05-21' UNION

    SELECT sdt = '20100521 01:02' UNION

    SELECT sdt = '20100521 01:02:03' UNION

    SELECT sdt = '20100521 01:02:03.004'

) tmp


以下方式是平台独立（不依赖于区域语言和日期格式设置）最常用方式（专家建议）：
'[yy]yymmdd[ hh:mi[:ss][.mmm]]'
貌似加上“-”分隔符也可以，但是不是完全平台独立没有测试过：
'[yy]yy-mm-dd[ hh:mi[:ss][.mmm]]'

详情参见《SQL Server 2005技术内幕：T-SQL程序设计》1.1节。


日期相关常见问题

1. 如果只想存日期或时间怎么办？

在SQL Server 2008中有date和time两种新增类型。
如果是2008之前的版本，可以用char(8)存储形如'20100826'的日期，用char(10)存储形如'12:10:35'的时间，并且在计算时可以方便地转换为datetime类型。如果想要节省空间，也可以用int存储天数和时间间隔（参考datetime的实现机制），但需要一点特殊的计算。用int存储形如20100826的数值表示日期也是一种办法，与char(8)的方案类似，但要多一步转换。

2. datetime类型要怎么查询？

查询20100826当天的数据


--以下查询对dtcolumn使用了函数，将不能使用索引

SELECT * FROM [table] WHERE DATEDIFF(day,dtcolumn,'20100826') = 0

--以下两种方法性能更好

SELECT * FROM [table] WHERE dtcolumn BETWEEN '20100826' AND '20100826 23:59:59' --只精确到秒，23:59:59之后一秒内的数据将无法查出

SELECT * FROM [table] WHERE dtcolumn >= '20100826' AND dtcolumn < '20100827'    --完全精确，但需要把dtcolumn列名写两遍


按天汇总指定时段数据


SELECT

	统计周期 = CONVERT(char(10),dtcolumn,120),

	汇总 = COUNT(*) --或其它聚合函数

FROM [table]

WHERE dtcolumn BETWEEN '20090101' AND '20101231 23:59:59'

GROUP BY CONVERT(char(10),dtcolumn,120)

ORDER BY 统计周期

--因为datetime按120转换为字符串的格式是2010-08-26 12:23:14，所以上面的CONVERT目标字符串长度改为4,7,10,13,16,19分别可以按年、月、日、时、分、秒统计。



新的日期时间类型

SQL Server 2008中新引入了date, time, datetime2, datetimeoffset几种类型。目前2008版本使用不多，暂不进行分析了。只要按照数据存储方式和可用值范围、相关的计算、比较、转换规则、支持的日期时间函数这样的顺序理清楚，问题自然迎刃而解。
...全文
6815 86 打赏 收藏 转发到动态 举报
写回复
用AI写文章
86 条回复
切换为时间正序
请发表友善的回复…
发表回复
cunyan_0519 2011-08-04
  • 打赏
  • 举报
 回复
谢谢分享
gaohaishancs 2011-08-04
  • 打赏
  • 举报
 回复

INSERT INTO sys_accounts (user_name,password,remarks,flag,account_id,operate_time) VALUES ('asd','asd', '123',1,1,"2011-08-04 10:47:38")


这句怎么执行不了
lily_0629 2011-07-09
  • 打赏
  • 举报
 回复
刚刚开始学习，有点糊涂
alain_song 2011-06-20
  • 打赏
  • 举报
 回复
好贴，学习ing
alain_song 2011-06-09
  • 打赏
  • 举报
 回复
Thanks.lz辛苦了！
lujz2010 2010-11-16
  • 打赏
  • 举报
 回复
学习！！！
skyaspnet 2010-09-01
  • 打赏
  • 举报
 回复
学习。。
redphase 2010-08-31
  • 打赏
  • 举报
 回复
[Quote=引用 72 楼 feilniu 的回复:]
比如该帖就是把字符串常量当作datetime常量的问题：
http://topic.csdn.net/u/20100831/10/96b71ddd-1c5d-4c30-9a30-3c44726ca915.html
[/Quote]

我也来报道，，，哈哈！~~明白了谢谢哈
feilniu 2010-08-31
  • 打赏
  • 举报
 回复
比如该帖就是把字符串常量当作datetime常量的问题：
http://topic.csdn.net/u/20100831/10/96b71ddd-1c5d-4c30-9a30-3c44726ca915.html
glamor_lau 2010-08-31
  • 打赏
  • 举报
 回复
呵呵 学习呀
熙风 2010-08-31
  • 打赏
  • 举报
 回复


那sqlite数据库里面的呢？每次下面两行代码就会报错。。。？？
info.ReceivedOn = Convert.ToDateTime(dr["ReceivedOn"]);
info.ReceivedOn = Convert.ToDateTime(dr["MsgFrom"].ToString());
不能读取
zwyeartwo 2010-08-31
  • 打赏
  • 举报
 回复
学习 。
昵称被占用了 2010-08-30
  • 打赏
  • 举报
 回复
关于datetime常量，请看看以下测试语句

declare @t datetime

set @t = getdate()

declare @i int

set @i = datediff(day,'1900-1-1',@t)

declare @n numeric(38,30)

set @n = datediff(ms,convert(varchar(10),@t,120),@t)/86400000.0 + @i



declare @s varchar(200)

set @s = '

declare @t datetime

set @t = ' + cast(@n as varchar(100)) + '

print convert(varchar(23),@t,121)'



print @s

print convert(varchar(23),@t,121)

exec(@s)
liju123456 2010-08-30
  • 打赏
  • 举报
 回复
<input type="text">学习
ghs79 2010-08-30
  • 打赏
  • 举报
 回复
写的很好，学习了。
feilniu 2010-08-30
  • 打赏
  • 举报
 回复
[Quote=引用 65 楼 haiwer 的回复:]

关于datetime常量，请看看以下测试语句

SQL code
declare @t datetime
set @t = getdate()
declare @i int
set @i = datediff(day,'1900-1-1',@t)
declare @n numeric(38,30)
set @n = datediff(ms,convert(varchar(10),@t,12……
[/Quote]

datetime常量是指自身就是datetime类型的常量，而不是可以隐式转换成datetime类型的varchar或decimal常量。



DECLARE @dt datetime;

SET @dt = '20100831 21:40:44';

SELECT @dt, --@dt是datetime类型变量

	@dt+1,  --直接加减天数

	CONVERT(varchar(30),@dt,121),CONVERT(char(8),@dt,112),CONVERT(char(8),@dt,108); --各种风格的输出



--varchar常量和decimal常量不是datetime常量

SELECT '20100831 21:40:44',

	--'20100831 21:40:44'+1,

	CONVERT(varchar(30),'20100831 21:40:44',121),CONVERT(char(8),'20100831 21:40:44',112),CONVERT(char(8),'20100831 21:40:44',108);

SELECT 40419.903287037036000,

	40419.903287037036000+1,

	CONVERT(varchar(30),40419.903287037036000,121);--CONVERT(char(8),40419.903287037036000,112),CONVERT(char(8),40419.903287037036000,108);


T-SQL不支持datetime类型的常量并不是什么缺陷。事实上，在实际开发中，变量的使用远比常量的使用多。
只是，在直接用T-SQL写查询的时候，常常会用常量进行运算或比较，这时候搞清楚常量自身的数据类型和表达式中发生的隐式转换非常重要。
feilniu 2010-08-30
  • 打赏
  • 举报
 回复
[Quote=引用 65 楼 haiwer 的回复:]

关于datetime常量，请看看以下测试语句

SQL code
declare @t datetime
set @t = getdate()
declare @i int
set @i = datediff(day,'1900-1-1',@t)
declare @n numeric(38,30)
set @n = datediff(ms,convert(varchar(10),@t,12……
[/Quote]

datetime常量，是指本身就是datetime类型的常量，而不是可以隐式转换成datetime类型的varchar常量和decimal常量。



DECLARE @dt datetime;

SET @dt = '20100831 21:40:44';

SELECT @dt, --@dt是datetime类型变量

	@dt+1,  --直接加减天数

	CONVERT(varchar(30),@dt,121),CONVERT(char(8),@dt,112),CONVERT(char(8),@dt,108); --各种风格的输出



--varchar常量和decimal常量不是datetime常量

SELECT '20100831 21:40:44',

	--'20100831 21:40:44'+1,

	CONVERT(varchar(30),'20100831 21:40:44',121),CONVERT(char(8),'20100831 21:40:44',112),CONVERT(char(8),'20100831 21:40:44',108);

SELECT 40419.903287037036000,

	40419.903287037036000+1,

	CONVERT(varchar(30),40419.903287037036000,121);--CONVERT(char(8),40419.903287037036000,112),CONVERT(char(8),40419.903287037036000,108);


T-SQL不支持datetime类型的常量并不是什么缺陷。事实上在实际开发中，变量的使用远比常量多，这一点并不影响开发。只是在直接使用T-SQL进行查询时，常常会指定一些常量进行运算，这时候搞清楚常量自身的数据类型以及表达式中发生的隐式转换是非常重要的。
wangtuwen 2010-08-30
  • 打赏
  • 举报
 回复
look
furtherchan 2010-08-30
  • 打赏
  • 举报
 回复
T-SQL不支持datetime类型的常量

不是不支持..是你不知道而已
MJunnnn 2010-08-30
  • 打赏
  • 举报
 回复
学习了，谢谢楼主
加载更多回复（57）
数据分析之datetime库的常见使用--字符串和datetime类型数据的相互转化
font color=#999AAA >说明：本blog基于python3 文章目录前言一、字符串转化为datetime类型数据二、datetime类型数据转化为字符串总结 前言 本文主要介绍datetime模块的字符串和datetime相互转化功能。 一、字符串转化为datetime类型数据 使用datetime.datetime.strptime(字符串，目标格式)方法 其中，目标格式有如下多种 %Y 四位年份 %y 二位年份 %m 二位月份 %d 二位日期 %H 24小时制 %I 12小时制
关于datetime类型范围查询
关于datetime类型我之前一直按照字符串去理解，也在很多时候它的确是这么表现的。例如使用 =、>、<、between '2019' and '2020'比较进行range查询。问题出在我知道字符串左前缀like 'A%'是使用索引的，而datetime会怪异一些… 一、创建一张表 创建一张表，有一个辅助字段state，观察字段为 date_start、date_end，分别使用...
从char数据类型到datetime数据类型的转换导致datetime值越界
      昨天,在公司里测试程序时,出现如"从char数据类型到datetime数据类型的转换导致datetime值越界"的错误.自已在网上终于找到了对一个解决方案,让我受到了很大启迪.自已随后分析数据库表的结构,原来数据表中的monthcode列是varchar(50)类型而不是datetime类型. 源码:"                     ..................
pandas datetime数据类型
datetime 数据类型 学习目标 应用Pandas来处理日期时间类型数据 1 Python的datetime对象 Python内置了datetime对象，可以在datetime库中找到 from datetime import datetime now = datetime.now() now 显示结果： datetime.datetime(2020, 6, 17, 19, 47, 56, 965416) 还可以手动创建datetime t1 = datetime.now() t2
Python Numpy库datetime类型的处理详解
##Python Numpy库datetime类型的处理详解 这篇文章主要介绍了Python Numpy库datetime类型的处理详解，Python中自带的处理时间的模块就有time 、datetime、calendar，另外还有扩展的第三方库，如dateutil等等。。当我们用NumPy库做数据分析时，如何转换时间呢？需要的朋友可以参考下 关于时间的处理，Python中自带的处理时间的模块就有time 、datetime、calendar，另外还有扩展的第三方库，如dateutil等等。通过这些途径可以
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章