Solved

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

Posted on 2014-02-25
7
572 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 50

Accepted Solution

by:
Gustav Brock earned 500 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 50

Expert Comment

by:Gustav Brock
ID: 39897813
You are welcome!

/gustav
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

733 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