sql server 数据库直接计算时间差

2017-12-13 12:02:26来源:oschina作者:Lucky_Me人点击

分享

1. sql server 数据库直接计算时间差


-- 计算两个时间差 结果是int类型


SELECT DATEDIFF(YEAR,'2005-12-29','2008-12-30') AS DiffDate -- 年


SELECT DATEDIFF(MONTH,'2005-12-29','2008-12-30') AS DiffDate -- 月


SELECT DATEDIFF(DAY,'2008-12-10','2008-12-30') AS DiffDate -- 日


SELECT DATEDIFF(HOUR,'2008-12-30 00:00:00','2008-12-30 10:20:00') AS DiffDate -- 小时


SELECT DATEDIFF(MI,'2008-12-30 00:00:00','2008-12-30 00:20:10') AS DiffDate -- 分钟


SELECT DATEDIFF(SECOND,'2008-12-30 00:00:00','2008-12-30 00:01:10') AS DiffDate -- 秒



2.时间长短截取


select top 5 a.monitor_time as '原本时间格式', convert(varchar(23),a.monitor_time ,121) as '23位数据格式', convert(varchar(19),a.monitor_time ,121) as '19位数据格式', convert(varchar(16),a.monitor_time ,121) as '16位数据格式', convert(varchar(13),a.monitor_time ,121) as '13位数据格式', convert(varchar(10),a.monitor_time ,121) as '10位数据格式' from t_air_monitor_factor_rtdata a order by monitor_time desc


效果:



微信扫一扫

第七城市微信公众平台