473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
1 2363
-----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?


Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: FilexBB | last post by:
Hi Folks, I have tried to redirect system.out for a while and then set it back, but it can't set it back as following program snapshot ByteArrayOutputStream baos = new ByteArrayOutputStream();...
4
by: Merlin | last post by:
Hi there, I would like to check if a string is a valid zip code via Javascript. Length and existents are already checked. How can I find out if the string contains characters other than...
5
by: Mike Carroll | last post by:
I have a COM server that's generally working ok. But one of its methods, when the IDL gets read by the Intertop layer, has parameters of type "out object". The C# compiler tells me that it...
4
by: Steve B. | last post by:
Hello I'm wondering what is exactly the difference between "ref" and "out" keywords. Thanks, Steve
2
by: Chua Wen Ching | last post by:
Hi there, I am wondering the difference between attribute and out keywords. Are they the same or does it serve any different purposes? I saw the and out usage in this code, and i had idea,...
4
by: Jon | last post by:
Why are out parmeters included in an BeginInvoke? They seem to do nothing? TestProgam: using System; namespace TempConsole { class App { public delegate void MyDelegate( out byte b, out...
14
by: stic | last post by:
Hi, I'm in a middle of writing something like 'exception handler wraper' for a set of different methodes. The case is that I have ca. 40 methods form web servicem, with different return values...
4
by: dlgproc | last post by:
I have a managed C++ DLL that contains the following: MyLib.h: // MyLib.h #pragma once using namespace System; using namespace System::Runtime::InteropServices; namespace MyLib { public...
6
by: nick | last post by:
For example: public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
6
by: carlos123 | last post by:
Ok guys, check this out! Im getting an error "Error: Index: 0, Size: 0" not sure why. try{ // Create file FileWriter fstream = new FileWriter("database.txt"); BufferedWriter...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.