34,590
社区成员
发帖
与我相关
我的任务
分享
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毫秒的间隔
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
--以下查询对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分别可以按年、月、日、时、分、秒统计。
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)
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);
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);