Does "1080617" look like a date?

[RossWindows]

I have a table that I have been asked to report on.
Upon linking the ODBC table, I realize that the date fields have entries like 1080617, 1080918, 1080710 etc.
I have searched through this forum looking for the answer on how to convert these to mm/dd/yyyy format but I've come up empty handed.
I swear I've the answer somewhere, but I can't find it!
I found one solution where a guy said ..."I use CDate, then add 200000, then subtract 4800.." but that gave me a bunch of gibberish.
Any thoughts?

Oh, almost forgot... The data type of the field is set to Decimal, precision 7 if that helps

 

[Rabbie]

They are certainly not Access dates. The numeric value of Today's date is 39841 if that helps.

What type of DB are you linking to via ODBC

 

[RuralGuy]

I've lost track of the author of this code but you may be dealing with Unix Epoch time. If so then this should help.

' Convert Unix Epoch time (Time in seconds since Jan 1, 1970)
'
' UTS_Offset is the hours offset from GMT where you are locate
' Eastern Time (US) = -5
' Central Time (US) = -6
' Mountain Time (US) = -7
' Pacific Time (US) = -8
'
Public Function fConvertEpoch(varEpochVal As Variant, UTC_OffSet As Integer) As Variant
Dim tmpDate As Date
Dim StartDaylight As Date
Dim EndDaylight As Date
 
If IsNull(varEpochVal) Then Exit Function
 
tmpDate = DateAdd("s", varEpochVal, #1/1/1970#)
tmpDate = DateAdd("h", UTC_OffSet, tmpDate)
 
' Get the last day of March by subtracting one day from 4/1
StartDaylight = DateAdd("d", -1, DateSerial(Year(tmpDate), 4, 1))
 
' Now skip to the next Sunday
StartDaylight = DateAdd("d", 5 - WeekDay(StartDaylight), StartDaylight)
StartDaylight = DateAdd("h", 2, StartDaylight)
EndDaylight = DateSerial(Year(tmpDate), 11, 1)
 
' Back up to the previous Sunday
EndDaylight = DateAdd("d", -5 + WeekDay(EndDaylight), EndDaylight)
EndDaylight = DateAdd("h", 1, EndDaylight)
 
If (tmpDate >= StartDaylight And tmpDate < EndDaylight) Then
  tmpDate = DateAdd("h", 1, tmpDate)
End If
 
fConvertEpoch = tmpDate
 
End Function

 

[The_Doc_Man]

I read those as oddball text-reformatted dates.

1080617 = 17 June 2008,
1080918 = 18 September 2008,
1080710 = 10 July 2009

Where the day is the last two digits, month equals next-to-last two digits, and year is the first three digits added to 1900.

Those aren't UNIX epoch dates. If you look at Windows internal time-line dates, the day number is on the order of 39,800 and change. If you look at UNIX dates, today is more on the order of 14,270 and change. (For OpenVMS, try day 54,860 give or take a couple).

Any chance that this is either a SYBASE or ORACLE database on the other end of the ODBC?

 

[RossWindows]

Ahh, that makes sence Doc. Every date that I tested against your theory came back okay. I'm pretty sure it's AS400 (which I don't know much about).

So now, If I wanted to build a query that returns the needed fields and the dates converted to mm/dd/yyyy, which method(s) should I use? I was thinking about the Left and Right methods, but I have a feeling there is a better way.