说明:
jde格式为int,如106251,其中106=2006-1900,251是当年的天数
sql server里创建函数:
create function f_date(@date int)
returns datetime
as
begin
declare @datetime datetime
set @datetime = dateadd(dd,right(@date,3)-1,cast(rtrim(1900+left(@date,3))+'0101' as datetime))
return @datetime
end
go
--函数将jde时间int格式转化为datetime
create function jdate(@offset int)
RETURNS int
as
begin
declare @now datetime
set @now=getdate()
declare @date datetime
set @date=dateadd(dd,-@offset,@now)
return ((year(@date)-1900)*1000+datediff(dd,cast(cast(year(@date) as nvarchar)+'0101' as datetime),@date)+1)
end
---函数用来设定时间段,将日期函数datetime转化为int
在excel中也可以这样:
DATE(INT(C23/1000),1,1)+MOD(C23,1000)-1