555~~~~ 我怎么这么笨呢!!!!!! bigint-->dateTime 精确到秒已经有办法了.不过是别人想出来的..... 先不公布答案,看看大家谁能想出来. ------------------ --bigint转换为dateTime型 CREATE function BigintToDatetime (@bigintValue bigint) returns Datetime as begin if @bigintValue is null return null declare @baseTime datetime set @baseTime = '1970-01-01 08:00:00.000' return dateadd(second,@bigintValue,@baseTime) end 这里的也不行的.只能实现68多年的区间.再长了还不行. 高手呢?
有办法,时间类型是通过8个字节位寸的这样就好了 select cast(cast(cast(cast(getdate() as binary(8)) as bigint ) as binary(8))as datetime)
duoluohuifeng(堕落回风)======== 也强,好象可以啊,我再试试.
duoluohuifeng(堕落回风)的方法好象真的可以啊 下面是使用他提供的方法写的datetime-->bigint 和bigint -->dateTime的方法. 大家帮忙看看有什么漏洞吗?俺觉得可以了. -------------- --bigint convert to datetime CREATE function BigIntConvertDateTime(@BigDateTime bigint) returns datetime as begin if @bigDateTime = null return null --return cast(cast( @bigDateTime as binary(8))as datetime) return convert(datetime,cast( @bigDateTime as binary(8)),120) end-------------------------------datetime convert to bigint CREATE function DateTimeConvertBigInt( @DateTime datetime) returns bigint as begin --select cast(cast(cast('2006-03-02 13:46:24.907' as datetime) as binary(8)) as bigint ) if @DateTime = null return 0 --dateTime style is correct? return cast(cast(@DateTime as binary(8)) as bigint ) end
数据就已经丢失了
转换回来 那当然是没有时间了你试一下select convert(bigint,convert(datetime,'1970-01-01 08:01:21',120)) --25567
select convert(bigint,convert(datetime,'1970-01-01 00:00:00',120)) --25567结果是一样的
---1970-01-01 08:01:20.997
-----------------------------
RETURNS bigint AS
BEGIN
if @time is null
return 0
--declare @time datetime
declare @start datetime
declare @day bigint
declare @hour bigint
declare @minute bigint
declare @ms bigint
set @Start ='1970-01-01 08:00:00'
set @day = datediff(dd,@Start,@time)
set @day =@day*24*3600*1000
set @hour = (datepart(hh,@time)-8)*3600*1000
set @minute = datepart(mi,@time)*60*1000
set @ms = datepart(ss,@time)*1000
return @day+@hour+@minute+@ms+datepart(ms,@time)
END????????????
和money转换有时会丢失部分数据的
CREATE FUNCTION [dbo].[DatetimeToBigint] (@time datetime)
RETURNS bigint AS
BEGIN
if @time is null
return 0
--declare @time datetime
declare @start datetime
declare @day bigint
declare @hour bigint
declare @minute bigint
declare @ms bigint
set @Start ='1970-01-01 08:00:00'
set @day = datediff(dd,@Start,@time)
set @day =@day*24*3600*1000
set @hour = (datepart(hh,@time)-8)*3600*1000
set @minute = datepart(mi,@time)*60*1000
set @ms = datepart(ss,@time)*1000
return @day+@hour+@minute+@ms+datepart(ms,@time)
END
--bigint转换为dateTime型,这个是我根据上面那个写的.
CREATE function BigintToDatetime (@bigintValue bigint)
returns Datetime
as
begin
if @bigintValue is null
return null
declare @baseTime datetime
--declare @bigintValue bigint
declare @intPart int
declare @decimalPart float
declare @mainPart varchar(10)
declare @hourPart varchar(2)
declare @minutePart varchar(2)
declare @SecondPart varchar(2) set @baseTime = '1970-01-01 08:00:00'
--set @bigintValue ='1136858461000'
--set @intPart = floor(@bigintValue/(24*3600*1000)) set @intPart = @bigintValue/(24*3600*1000)
set @decimalPart = ((convert(float,@bigintValue))/(24*3600*1000)) - @intPart -- set @decimalPart =
set @mainPart = convert(varchar(10),dateadd(day,@intPart,@baseTime),120)
--select @intPart
--select @decimalPart
--select @mainPart set @intPart = @decimalPart*24
set @decimalPart = (@decimalPart*24) - @intPart
set @hourPart = datepart(hour,dateadd(hour,@intPart,@baseTime))
--select @intPart
--select @decimalPart
--select @hourPart set @intPart = @decimalPart*60
set @decimalPart = (@decimalPart*60)-@intPart
set @minutePart = datepart(minute,dateadd(minute,@intPart,@baseTime))
--select @minutePart
set @intPart = @decimalPart*60
set @decimalPart = (@decimalPart*60) - @intPart
set @secondPart = datepart(second,dateadd(second,@intPart,@baseTime))
--select @secondPart return convert(datetime, @mainPart+' '+@hourPart+':'+@minutePart+':'+@secondPart,120)
end问题:
1.转换结果不正确
--1946-12-01 03:01:01 datetimeTobigint--> 728542739000 bigintTodatetime--> 1946-12-01 04:02:02.000 (不正确)
--2006-12-01 03:01:01 datetimeTobigint--> 1164913261000 bigintTodatetime--> 2006-11-30 03:01:01.000 (不正确)
--2006-12-1 13:01:01 datetimeTobigint--> 1164949261000 bigintTodatetime--> 2006-12-01 13:01:01.000 (正确)2.DatetimeToBigint 里为什么要转化成毫秒呢?谢谢了,可以帮我搞定吗?
有人说dateTime 转换为bigint 保存这样可以保持兼容性sqlServer和Oracle
解决问题的方法就是分段记录时间,将bigint的每两位记录分别记录一个月/日/小时/分钟/秒而年和毫秒需要四位和三位。其实解决问题的办法等于不解决。
如果真的需要计算距今多少天可以直接写一个查询或视图,随时查询/随时更新那是最好的解决方案。
但我算过了.一百年的话毫秒是3153600000000.即使1万年,bigint也可以保存的下.
我怎么这么笨呢!!!!!!
bigint-->dateTime 精确到秒已经有办法了.不过是别人想出来的.....
先不公布答案,看看大家谁能想出来.
我怎么这么笨呢!!!!!!
bigint-->dateTime 精确到秒已经有办法了.不过是别人想出来的.....
先不公布答案,看看大家谁能想出来.
------------------
--bigint转换为dateTime型
CREATE function BigintToDatetime (@bigintValue bigint)
returns Datetime
as
begin
if @bigintValue is null
return null
declare @baseTime datetime
set @baseTime = '1970-01-01 08:00:00.000'
return dateadd(second,@bigintValue,@baseTime)
end
这里的也不行的.只能实现68多年的区间.再长了还不行.
高手呢?
我的方法有误差的.
使用加MS的办法,日期区间有太小.
还有别的变通的方法吗?
SKSHELP
select cast(cast(cast(cast(getdate() as binary(8)) as bigint ) as binary(8))as datetime)
也强,好象可以啊,我再试试.
下面是使用他提供的方法写的datetime-->bigint 和bigint -->dateTime的方法.
大家帮忙看看有什么漏洞吗?俺觉得可以了.
--------------
--bigint convert to datetime
CREATE function BigIntConvertDateTime(@BigDateTime bigint)
returns datetime
as
begin
if @bigDateTime = null
return null
--return cast(cast( @bigDateTime as binary(8))as datetime)
return convert(datetime,cast( @bigDateTime as binary(8)),120)
end-------------------------------datetime convert to bigint
CREATE function DateTimeConvertBigInt( @DateTime datetime)
returns bigint
as
begin
--select cast(cast(cast('2006-03-02 13:46:24.907' as datetime) as binary(8)) as bigint )
if @DateTime = null
return 0
--dateTime style is correct?
return cast(cast(@DateTime as binary(8)) as bigint )
end
楼主能不能实验一下呢