日期:2014-05-18  浏览次数:20629 次

时间转数值这些如何在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) 
--结果:39097
select 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 code
set nocount on
declare @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 code
DECLARE @T TABLE (
send_time2 DATETIME,SENDTIME INT
)

INSERT @T SELECT
'2007-01-16 21:10:40', 1168953040
UNION ALL SELECT
'2008-01-24 19:00:10', 1201172410
UNION ALL SELECT
'2007-11-05 16:24:21', 1194251061
UNION ALL SELECT
'2007-06-05 16:42:59', 1181032979
UNION ALL SELECT
'2007-10-12 12:03:29', 1192161809
UNION ALL SELECT
'2007-01-17 11:38:19', 1169005099
UNION ALL SELECT
'2007-06-01 16:25:59', 1180686359

SELECT *,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    0
2008-01-24 19:00:10.000    1201172410    1198522810    0
2007-11-05 16:24:21.000    1194251061    1191601461    0
2007-06-05 16:42:59.000    1181032979    1178383379    0
2007-10-12 12:03:29.000    1192161809    1189512209    0
2007-01-17 11:38:19.000    1169005099    1166355499    0
2007-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
*/

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