Par2: Trigger to convert MS SQL date to Clarion date 2004-06-11 -- Leslie Dean I'm not sure if this is what you want, but I have a stored procedure that I
ship with FM3...
CREATE procedure ds_ConvertClarionDateTime @ClaDate int = 0, @ClaTime int =
0, @SqlDateTime datetime output
as
declare @SqlDate int -- date days
declare @SqlTime decimal(38,12) -- time days
declare @ClaRoot datetime
declare @MssqlRoot datetime
declare @OneMssqlSecond decimal(38,12)
set @OneMssqlSecond = 0.000011574077 -- 1 mssql second in days
set @ClaRoot = 'Dec 28, 1800' -- Root date for Clarion Date
set @MSSQLRoot = 'Jan 01, 1900' -- Root date for MSSQL Date
set @SqlDate = @ClaDate - datediff(day,@ClaRoot,@MssqlRoot) -- clarion
date - the difference between root dates
set @SqlTime = (@ClaTime / 100) * @OneMssqlSecond -- clarion time / 100
* day value of mssql seconds
if @ClaDate > 2994626 -- invalid Clarion Date
return 1
if @ClaTime > 8640000 -- invalid Clarion Time
return 2
if @ClaDate = 0
begin
if @ClaTime = 0
return -1 -- zero date and time
else
set @SqlDateTime = convert(datetime,@SqlTime,0) -- convert to
datetime type with only time - date = root
end
else
begin
if @ClaTime = 0
set @SqlDateTime = convert(datetime,@SqlDate,0) -- convert to
datetime type with only date - time = root
else
begin
set @SqlDateTime = @SqlDate + @SqlTime -- add date + time
set @SqlDateTime = convert(datetime,@SqlDateTime,0) -- convert to
datetime
end
end
GO
Printed November 21, 2024, 12:52 pm This article has been viewed/printed 35275 times. |