Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date Format on Table unrecognized (Tables is from Amano Time Guardian Software)

Posted on 2014-02-25
7
Medium Priority
?
602 Views
Last Modified: 2014-03-01
I am trying to understand the format of dates on the Table (Denormalized_Table1) attached, I copied the table to Microsoft Access for your revision.

I thought it was a Julian Date but is not. The Table information is from the Time Clock software called Amano Time Guardian, which was originally stored on a Firebird Server.

I need to prepare a report of puch in and punch out, with some special requirements but if I am not able to convert those dates to regular dates I wont be able to work.

Date fields are: DENCALCDATE, DENCACTUALOUTPUNCH and several others.

Please, if someone recognizes this format and tell me how to convert it I will appreciate the help.

Thanks and Regards,
TimeGuardianSoftwareTable.zip
0
Comment
Question by:mmoralespr
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39887804
It is a UNIX date (base date is January 1, 1970) and includes milliseconds.

If you don't care about the milliseconds do something like this:
DECLARE @UNIXDate int = 1386028800000 / 86400000
SELECT DATEADD(day, @UNIXDate, '19700101')

Suerte.
0
 

Author Comment

by:mmoralespr
ID: 39887878
I did a query using your formula and it is pretty near at the real dates but not completely accurate. The table is from a real time clock system and when I convert those date values from unix to regular format dates it is telling me that some people punched in on 2/28/2014 and that is not true because today we are at 2/26/2014.

I did some minor changes to the formula in order to work in Access:

TestDate: DateAdd("d",([DENCALCDATE]/86400000),#1/1/1970#)

Thanks for your input! Let me know if you notice something in the formula.

Regards,
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39888061
It shouldn't work this simple way as the value for "d" is way to large.
However, you can use a function like this:
Public Function DateFromUnix( _
  ByVal dblSeconds As Double, _
  Optional lngLocalTimeBias As Long) _
  As Date
  
' Converts UNIX (or POSIX) time Value to UTC date Value.
' Optionally, a local time bias can be specified;
' this must be in minutes with a resolution of 15 minutes.
'
' Examples:
'   UTC. dblSeconds: 1000000000, lngLocalTimeBias: 0
'     2001-09-09 01:46:40
'   CET. dblSeconds: 1000000000, lngLocalTimeBias: -60
'     2001-09-09 02:46:40
'
' 2004-03-23. Cactus Data ApS. CPH.
' 2008-02-27. Constants renamed for clarity.
  
  ' UNIX epoch (start time).
  Const cdatUnixEpoch       As Date = #1/1/1970#
  ' Maximum time bias in seconds, 12 hours.
  Const clngSecondsBiasMax  As Long = 12& * 60&
  ' Count of seconds of one day.
  Const clngSecondsPerDay   As Long = 24& * 60& * 60&
  
  Dim dblDays               As Double
  Dim dblDaysSeconds        As Double
  Dim lngSeconds            As Long
  Dim datTime               As Date
  
  ' Limit intervals for DateAdd to Long to be acceptable.
  dblDays = Int(dblSeconds / clngSecondsPerDay)
  dblDaysSeconds = dblDays * clngSecondsPerDay
  lngSeconds = dblSeconds - dblDaysSeconds
  
  datTime = DateAdd("d", dblDays, cdatUnixEpoch)
  datTime = DateAdd("s", lngSeconds, datTime)
  If lngLocalTimeBias <> 0 Then
    If Abs(lngLocalTimeBias) < clngSecondsBiasMax Then
      datTime = DateAdd("n", -lngLocalTimeBias, datTime)
    End If
  End If
  
  DateFromUnix = datTime

End Function

Open in new window

And should you need to reverse conversion:
Public Function UnixTimeValue( _
  ByVal datTime As Date, _
  Optional ByVal lngLocalTimeBias As Long) _
  As Double
  
' Converts UTC date Value to UNIX (or POSIX) time Value.
' Optionally, a local time bias can be specified;
' this must be in minutes with a resolution of 15 minutes.
'
' Examples:
'   UTC. datTime: #09/09/2001 01:46:40#, lngLocalTimeBias: 0
'     1000000000
'   CET. datTime: #09/09/2001 02:46:40#, lngLocalTimeBias: -60
'     1000000000
'
' 2004-03-23. Cactus Data ApS. CPH.
' 2008-02-27. Constants renamed for clarity.
  
  ' UNIX epoch (start time).
  Const cdatUnixEpoch       As Date = #1/1/1970#
  ' Maximum time bias in seconds, 12 hours.
  Const clngSecondsBiasMax  As Long = 12& * 60&
  
  Dim dblSeconds            As Double
  
  If lngLocalTimeBias <> 0 Then
    If Abs(lngLocalTimeBias) < clngSecondsBiasMax Then
      datTime = DateAdd("n", lngLocalTimeBias, datTime)
    End If
  End If
  dblSeconds = DateDiff("s", cdatUnixEpoch, datTime)
  
  UnixTimeValue = dblSeconds

End Function

Open in new window

/gustav
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:mmoralespr
ID: 39890811
Hi Gustav,

I tried the code in Microsoft Access but when I used the formula on a query I am receiving a run time error that stops in this part of the code:

datTime = DateAdd("d", dblDays, cdatUnixEpoch)

Maybe there is a mistype on someplace at the code, but I don't know. Can you notice anything?

Regards,
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39897154
Yes, that's because your time values in the table are in integer milliseconds.
The function expects a count of seconds.

Adjust your expression in the query to this:

TestDate2: DateFromUnix([DENCALCDATE]/1000)

/gustav
0
 

Author Closing Comment

by:mmoralespr
ID: 39897260
Thanks for your support, it worked like a charm.  Many thanks again!

Take Care!
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39897813
You are welcome!

/gustav
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question