Print Page | Close Window

BIGINT to DateTime

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10266
Printed Date: 29 Apr 2024 at 9:54pm


Topic: BIGINT to DateTime
Posted By: Nissim
Subject: BIGINT to DateTime
Date Posted: 15 Jun 2010 at 11:37pm
Hello experts,
I have this function in MS SQL that converts BIGINT to datetime. The problem is that my customer not allows using DB functions as a security policy in the organization.
The solution is to make the conversion in Crystal formula, but I don't know how to translate this MS SQL function to Crystal Formula. Please help.
The function is:
ALTER FUNCTION [dbo].[udfTicksToDateTime]
(
      @Ticks BIGINT
)
RETURNS DATETIME
AS
BEGIN
      DECLARE @Days BIGINT
      DECLARE @DaysBefore1753 BIGINT
      DECLARE @TimeTicks BIGINT
      DECLARE @Seconds BIGINT

      SET @Days = @Ticks / CONVERT(BIGINT,864000000000)
      SET @DaysBefore1753 = CONVERT(BIGINT,639905)
      SET @TimeTicks = @Ticks % CONVERT(BIGINT,864000000000)
      SET @Seconds = @TimeTicks / CONVERT(BIGINT,10000000)

      RETURN DATEADD(s,@Seconds,DATEADD(d,@Days - @DaysBefore1753,CONVERT(DATETIME,'1/1/1753')))
END


Replies:
Posted By: lockwelle
Date Posted: 16 Jun 2010 at 4:16am

local numbervar iDays := {table.ticks} / 864000000000;
local numbervar iDaysBefore1753 = 639905
local numbervar iTimeTicks = {table.ticks} mod 864000000000
local numbervar iSeconds = iTimeTicks / 10000000

DATEADD(s,iSeconds,DATEADD(d,iDays - iDaysBefore1753,CDATE('1/1/1753')))

this is my stab at it.
 
Hope I'm close

Posted By: Nissim
Date Posted: 16 Jun 2010 at 5:21am
Thanks a lot! It's almost 100%.
To be more precise, the divide sign should be backslash (\) otherwise it won't work.


Print Page | Close Window