By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 2,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Huh Oh... I'm out-of-range on a UNIX date conversion. Why?

P: n/a
MLH
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?
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----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 E324 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?


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.