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

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
mmoralesprAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
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
 
Anthony PerkinsCommented:
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
 
mmoralesprAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
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
 
mmoralesprAuthor Commented:
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
 
mmoralesprAuthor Commented:
Thanks for your support, it worked like a charm.  Many thanks again!

Take Care!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.