convert函数用法小结---转载
1.遇见一个应用,需要按照每天进行数据统计,比如里程,油耗量,但是数据库里面存储的时间太精细。在sqlserver下面通过下面的方法解决
convert(varchar(10), 时间字段, 120)
这样就把时间字段解析的只剩下日期了,没有了时分秒,可以通过group by来对日期进行分组了。
比如:
select m.DAYTIME,sum(m.OILUSED) as OILUSED,sum(m.ALLMILE) as mile from
(
select a.OILUSED as OILUSED ,a.DRIVE_MILE as ALLMILE ,convert(char(10),a.GPSTIME,120) as DAYTIME from
(select t.DEVICE_ID,t.GPSTIME,t.OIL_USED as OILUSED,t.DRIVE_MILE from T_TERM_STATUS_RECORD t) a,
(select v.id as vehicle_id,r.tid from ref_vehicle_terminal r,t_vehicle v where r.vid=v.id) c
where a.DEVICE_ID=c.TID
) m group by m.DAYTIME
2.第二个应用,某字段是verchar类型,但是我想用sum求和,怎么办?
sum(convert(numeric(18,3),c.CHARGE))
详细了解convert()函数可以移步
http://www.yesky.com/imagesnew/software/tsql/ts_ca-co_2f3o.htm
convert(varchar(10), 时间字段, 120)
这样就把时间字段解析的只剩下日期了,没有了时分秒,可以通过group by来对日期进行分组了。
比如:
select m.DAYTIME,sum(m.OILUSED) as OILUSED,sum(m.ALLMILE) as mile from
(
select a.OILUSED as OILUSED ,a.DRIVE_MILE as ALLMILE ,convert(char(10),a.GPSTIME,120) as DAYTIME from
(select t.DEVICE_ID,t.GPSTIME,t.OIL_USED as OILUSED,t.DRIVE_MILE from T_TERM_STATUS_RECORD t) a,
(select v.id as vehicle_id,r.tid from ref_vehicle_terminal r,t_vehicle v where r.vid=v.id) c
where a.DEVICE_ID=c.TID
) m group by m.DAYTIME
2.第二个应用,某字段是verchar类型,但是我想用sum求和,怎么办?
sum(convert(numeric(18,3),c.CHARGE))
详细了解convert()函数可以移步
http://www.yesky.com/imagesnew/software/tsql/ts_ca-co_2f3o.htm
作者:江宁织造
博客:http://www.cnblogs.com/wgx0428/
博客:http://www.cnblogs.com/wgx0428/