Convert GMT Time to Time it Actually Happened
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22985
Printed Date: 29 Apr 2024 at 3:36am
Topic: Convert GMT Time to Time it Actually Happened
Posted By: jsh8286
Subject: Convert GMT Time to Time it Actually Happened
Date 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.
|
Replies:
Posted By: kevlray
Date 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.
|
Posted By: lockwelle
Date 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
|
Posted By: jsh8286
Date 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.
|
Posted By: lockwelle
Date 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...
|
Posted By: lockwelle
Date Posted: 09 Nov 2021 at 11:48am
|
GMT is 4-5 hours ahead of Eastern, hopefully that helps
|
|