Convert Date/Time in VBA

[kidkosmo]

Hi, All,

I am pulling data from a data source in which the format is the Long
Date format (i.e. "1/1/09 12:00:00 AM") and inserting it into another
table. The original data source as the time for the GMT timezone and
I want to convert that to EST timezone (i.e. 1/1/09 12:01:00 AM
becomes 12/31/2008 7:01:00 PM). Here's how I have my SQL statement
created:

strsql2 = "INSERT INTO TestTheory (OPEN_TIME, OpenDate, OpenTime)
VALUES ('" & _
rst!OPEN_TIME & _
strSep & _
Format$(rst!OPEN_TIME, "Short Date") & _
strSep & _
Format$(rst!OPEN_TIME, "Short Time") & _

How can I quickly recalculate rst!OPEN_TIME to subtract 5 hours so
that I get the correct Eastern timezone date and time when I reformat
for the additional columns?

Thanks!!!

 

[Douglas J. Steele]

You really should be storing date and time in a single field. That's the way
the Date data type was designed in Access: it's an eight-byte floating point
number where the integer portion represents the date as the number of days
relative to 30 Dec, 1899 and the decimal portion represents the time as a
fraction of a day. When you only store a date, you're actually storing that
date at midnight. When you only store a time, you're actually storing that
time on 30 Dec, 1899.

Should you have occasion where you only need the date portion, or you only
need the time portion, you can use the DateValue or TimeValue functions.

In any case, the answer to your specific question is the DateAdd function:

DateAdd("h", -5, rst!OPEN_TIME)

 

[John W. Vinson]

Hi, All,

I am pulling data from a data source in which the format is the Long
Date format (i.e. "1/1/09 12:00:00 AM") and inserting it into another
table. The original data source as the time for the GMT timezone and
I want to convert that to EST timezone (i.e. 1/1/09 12:01:00 AM
becomes 12/31/2008 7:01:00 PM). Here's how I have my SQL statement
created:

strsql2 = "INSERT INTO TestTheory (OPEN_TIME, OpenDate, OpenTime)
VALUES ('" & _
rst!OPEN_TIME & _
strSep & _
Format$(rst!OPEN_TIME, "Short Date") & _
strSep & _
Format$(rst!OPEN_TIME, "Short Time") & _

How can I quickly recalculate rst!OPEN_TIME to subtract 5 hours so
that I get the correct Eastern timezone date and time when I reformat
for the additional columns?

Thanks!!!

First off... splitting a date/time value into separate date and time fields is
just going to give you problems. You'll need to convert them or combine them
later for searching and reporting.

An Access Date/Time value, regardless of format, is stored as a Double Float
count of days and fractions of a day (times) since midnight, December 30,
1899. The format is not stored in the data, it just controls how the data is
displayed. You can use an expression like

DateAdd("h", 5, [OPEN_TIME])

to calculate the EST time, either in your append query or in an Update query.
Accounting for daylight savings time makes life a bit more complex!