Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Convert GMT Time to Time it Actually Happened Post Reply Post New Topic
Author Message
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 45
Quote jsh8286 Replybullet Topic: Convert GMT Time to Time it Actually Happened
    Posted: 08 Nov 2021 at 3:51pm
We have an accounting system that keeps track for audit purposes who did what on what date and at what time on that date.

Long story short not the date but the time associated with that date is in GMT. So we "simply" would like to convert this GMT number to the time it happened on that day.

So for example the field in question is called;

AUDTIME

and here is one of the numbers it carries behind the scenes in the database:

16,111,171.00

This is a Time field in the database but the number as shown in the example doesn't mean much to us. We would like to convert this time to know what time it happened on the day it happened. FWIW we are in the Eastern TZ if this makes a difference.

Thanks in advance for any postings.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1568
Quote kevlray Replybullet Posted: 09 Nov 2021 at 4:34am
I am not sure that the function ShiftDateTime would work in this situation since you need a datetime and I am not sure how that number would covert to a datetime.  Also there is a dateadd function.  But then again you would need a datetime.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4362
Quote lockwelle Replybullet Posted: 09 Nov 2021 at 5:08am
It is probably the number of seconds from some fixed date.
If you know the date of one of the transactions, try doing a DATEDIFF(s, -number of seconds, 'the transaction date'). I would do it in SQL, as it is easier to change things and the syntax is slightly different in Crystal.

Once you get a date/time, you can work on adjusting GMT to your time. Daylight savings will be another wrinkle that you will have to figure out...GMT doesn't use it...and it changes every year.

HTH
IP IP Logged
jsh8286
Groupie
Groupie
Avatar

Joined: 09 Mar 2012
Online Status: Offline
Posts: 45
Quote jsh8286 Replybullet Posted: 09 Nov 2021 at 7:48am
Thanks for your posts much appreciated. If it does help there is an associated AUDDATE field as well. My bad I should have mentioned this but didn't think it would be required.

Not sure if this helps.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4362
Quote lockwelle Replybullet Posted: 09 Nov 2021 at 11:26am
The date should be able to assist you in figuring out how to decode the number.

typically, dates like that are the number of seconds, though doing that math, comes out to 186 days.

The next thought is, it is the number of milliseconds since midnight on the AudDate field. The 16,111,171 in milliseconds is 4:28:31 am, approximately.

Maybe this helps...
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4362
Quote lockwelle Replybullet Posted: 09 Nov 2021 at 11:48am
GMT is 4-5 hours ahead of Eastern, hopefully that helps
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.