Solved

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

Posted on 2014-02-25
7
568 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 49

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the 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 49

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 49

Expert Comment

by:Gustav Brock
ID: 39897813
You are welcome!

/gustav
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

790 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