Login
Templates, Tools and Utilities
for Clarion Developers
Templates, Tools and Utilities
|
||
|
|
|
Add a comment to an Icetips ArticlePlease add your comments to this article.
Please note that you must provide both a name and a valid email address in order
for us to publish your comment. Comments are moderated and are not visible until they have been approved. Spam is never approved!
Back to article list Search Articles Add Comment Printer friendly Direct link 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
Today is November 23, 2024, 3:28 am This article has been viewed 35278 times.
|
|