时间转数值这些怎么在mssql中实现_Sql Server

时间转数值这些如何在mssql中实现
看了一下mysql的数据，发现有两个字段，
send_time2 send_time
2007-01-16 21:10:40 1168953040
2008-01-24 19:00:10 1201172410
2007-11-05 16:24:21 1194251061
2007-06-05 16:42:59 1181032979
2007-10-12 12:03:29 1192161809
2007-01-17 11:38:19 1169005099
2007-06-01 16:25:59 1180686359

send_time2是日期型，send_time是数值型，但内容是一样的，mssql中

SQL code

select cast(cast('2007-01-16 21:10:40' as datetime) as numeric) --结果:39097select cast(cast('2007-01-16 21:10:40' as datetime) as float)   --结果:39096.8824074074

数值与mysql中的都不一样，请问高手如何在mssql中实现与mysql中一样的结果数值。

------解决方案--------------------

SQL code

这应该是以某个时间点为基准 和给出的日期做了某种差(年、月、日、时、分、秒、毫秒）。比如以 '1970-01-01 08:00:00.000'这个日期为基准的话'2007-01-16 21:10:40' 这个日期得到的数值就是 select DATEDIFF (second , '1970-01-01 08:00:00.000' , '2007-01-16 21:10:40.000')仅作参考
------解决方案--------------------
SQL codeset nocount ondeclare @tmp table (dtm datetime, tpl int)insert into @tmp values ('2007-01-16 21:10:40', 1168953040)insert into @tmp values ('2008-01-24 19:00:10', 1201172410)insert into @tmp values ('2007-11-05 16:24:21', 1194251061)insert into @tmp values ('2007-06-05 16:42:59', 1181032979)insert into @tmp values ('2007-10-12 12:03:29', 1192161809)insert into @tmp values ('2007-01-17 11:38:19', 1169005099)insert into @tmp values ('2007-06-01 16:25:59', 1180686359)-- MySQL以1970-1-1为零时，加上时区调整的8小时select *    , dateadd(second, tpl, '1970-1-1 8:00')     , datediff(second, '1970-1-1 8:00', dtm)from @tmp-- dtm,tpl,,-- 2007-01-16 21:10:40.000,1168953040,2007-01-16 21:10:40.000,1168953040-- 2008-01-24 19:00:10.000,1201172410,2008-01-24 19:00:10.000,1201172410-- 2007-11-05 16:24:21.000,1194251061,2007-11-05 16:24:21.000,1194251061-- 2007-06-05 16:42:59.000,1181032979,2007-06-05 16:42:59.000,1181032979-- 2007-10-12 12:03:29.000,1192161809,2007-10-12 12:03:29.000,1192161809-- 2007-01-17 11:38:19.000,1169005099,2007-01-17 11:38:19.000,1169005099-- 2007-06-01 16:25:59.000,1180686359,2007-06-01 16:25:59.000,1180686359-- 第一二列为原始数据；-- 第三列为由第二列转换的日期时间-- 第四列为由第一列换算的秒值
------解决方案--------------------
呵呵，看一下测试


SQL codeDECLARE @T TABLE (send_time2 DATETIME,SENDTIME INT)INSERT @T SELECT'2007-01-16 21:10:40', 1168953040UNION ALL SELECT'2008-01-24 19:00:10', 1201172410UNION ALL SELECT'2007-11-05 16:24:21', 1194251061UNION ALL SELECT'2007-06-05 16:42:59', 1181032979UNION ALL SELECT'2007-10-12 12:03:29', 1192161809UNION ALL SELECT'2007-01-17 11:38:19', 1169005099UNION ALL SELECT'2007-06-01 16:25:59', 1180686359SELECT *,DATEDIFF(SECOND,'1970-1-1 8:00',send_time2),DATEDIFF(SECOND,'1970-1-1 8:00',send_time2)-SENDTIME FROM @T -- 结果send_time2    SENDTIME    (无列名)    (无列名)2007-01-16 21:10:40.000    1168953040    1166303440    02008-01-24 19:00:10.000    1201172410    1198522810    02007-11-05 16:24:21.000    1194251061    1191601461    02007-06-05 16:42:59.000    1181032979    1178383379    02007-10-12 12:03:29.000    1192161809    1189512209    02007-01-17 11:38:19.000    1169005099    1166355499    02007-06-01 16:25:59.000    1180686359    1178036759    0
------解决方案--------------------
探讨
SQL code
select DATEDIFF (second , '1970-01-01 08:00:00.000' , '2007-01-16 21:10:40.000')
/*
(无列名)
1168953040
*/

这种方法应该就是你想要的。