-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The number value in the DateAdd() function is a Long data type, which
has a range from -2,147,483,648 to 2,147,483,647.
When I tested DateAdd() using 9 999 999 999 I got an overflow error -
because 9 billion is outside the valid Long numeric range.
There is a good essay about Unix time here:
http://en.wikipedia.org/wiki/Unix_time
which may help you.
Here is a function that I created from an SQL function I found on the
internet. It hasn't been rigorously tested.
Function convUnixDate(dblUnixDate As Double) As Date
' Purpose:
' Convert the Unix date (seconds) to date/time data type
' In:
' dblUnixDate Seconds since Jan 1, 1970 00:00:00
' Use a Double data type 'cuz it's range is:
' 4.94065645841247 E–324 to 1.79769313486231 E308
' Don't use any decimal places.
' Out:
' Date/Time
' Created:
' mgf 23oct2004 Converted from web site:
'
http://www.experts-exchange.com/
' Databases/Microsoft_SQL_Server/Q_20655264.html
' Comment from rdmjrb, Date: 06/24/2003 07:58AM PDT
'
' ALTER FUNCTION convUnixDate (@unixDate BIGINT)
' RETURNS VARCHAR(24)
' AS
' Begin
' DECLARE @tmpDate datetime
' SET @tmpDate='1970-01-01 00:00'
' WHILE @unixDate > 2000000000
' Begin
' SET @tmpDate=DATEADD(SECOND, 2000000000, @tmpDate)
' SET @unixDate=@unixDate-2000000000
' End
' SET @tmpDate=DATEADD(SECOND, @unixDate, @tmpDate)
'
' RETURN CONVERT(CHAR(11), @tmpDATE, 101) +
' SUBSTRING(CONVERT(CHAR(20), @tmpDATE, 100), 13, 7)
' END --FUNCTION
'
' Modified:
' mgf 23oct2004 Converted to VBA
Const lngLimit As Long = 2000000000
Dim dteTemp As Date
dteTemp = CDate("1970-01-01 00:00:00") ' Unix start time
Do While dblUnixDate > lngLimit
dteTemp = DateAdd("s", lngLimit, dteTemp)
dblUnixDate = dblUnixDate - lngLimit
Loop
convUnixDate = DateAdd("s", dblUnixDate, dteTemp)
End Function
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQXrrRYechKqOuFEgEQK1rwCbBCrUk55xltITdz4vnqAVcZ 6pgoQAoJtd
vOsL88F1E1wMa5KKIcWwSR/G
=H98c
-----END PGP SIGNATURE-----
MLH wrote:
In an Access 97 form, I have a textbox control with the following
code that runs AfterUpdate...
Option Compare Database
Option Explicit
Private Sub UNIXdate_AfterUpdate()
Me!RealDate = DateAdd("s", [UNIXdate], #1/1/1970#)
End Sub
If I type in 9 999 999 999 and press ENTER, the control below
displays 9/9/2001 1:46:39AM. But if I type 10 000 000 000 in
the UNIXdate control and press ENTER, I get an error that says
Runtime error '6'
Overflow
Do I need to change the code in UNIXdate or something with
the RealDate control?