sql 时间取日期部分速度对比

最新推荐文章于 2022-03-16 16:51:21 发布

Fifi_0617

最新推荐文章于 2022-03-16 16:51:21 发布

阅读量509

点赞数

分类专栏：互联网及相关

本文链接：https://blog.csdn.net/zyfzhangyafei/article/details/79531732

版权

互联网及相关专栏收录该内容

68 篇文章 0 订阅

订阅专栏

select CONVERT(varchar(100), etime, 23),COUNT(imsi) from dbo.userEventInfo_partition
where etime >'20180101' and etime <'20180201'
group by CONVERT(varchar(100), etime, 23)

order by CONVERT(varchar(100), etime, 23)

耗时　：　31Ｓ

select Datename(year,etime)+'-'+Datename(month,etime)+'-'+Datename(day,etime),COUNT(imsi) from dbo.userEventInfo_partition where etime >'20180101' and etime <'20180201'
group by Datename(year,etime)+'-'+Datename(month,etime)+'-'+Datename(day,etime)

order by Datename(year,etime)+'-'+Datename(month,etime)+'-'+Datename(day,etime)

耗时　：　1Ｓ

可见字符处理是很耗时的。

补齐日期的两位数写法：

select Datename(YYYY,etime)+'-'+Datename(M,etime)+'-'+RIGHT('0'+ltrim(Datename(DD,etime)),2),COUNT(imsi) from dbo.userEventInfo_partition where etime >'20180101' and etime <'20180201'
group by Datename(YYYY,etime)+'-'+Datename(MM,etime)+'-'+RIGHT('0'+ltrim(Datename(DD,etime)),2)
order by Datename(YYYY,etime)+'-'+Datename(MM,etime)+'-'+RIGHT('0'+ltrim(Datename(DD,etime)),2)