Converting Date & Time between time zones

[aireq]

I have a source of data that provides a set of date and time values in UTC in
the following format:

2008-07-01 06:35:00.0

Yet I need to convert this to Pacific time (-8 hours). I can't just
substract 8 hours because if the time is 8:00 AM UTC or earlier then the
conversion needs to know to go back one day as well.

Is there an easy way to do this? It doesn't seem that access has a combined
date and time functions.


Eric

 

[Jerry Whittle]

DateAdd("h",-8,"2008-07-01 06:35:00")

However there's a little problem. Notice that the .0 is missing. Access
doesn't handle less than a second. therefore you would have to strip out that
part something like this:.

DateAdd("h",-8,Left("2008-07-01 06:35:00.0",19))

Also what will you do about daylight savings time changes?

 

[Douglas J. Steele]

The built-in DateAdd function should take care of that for you:

DateAdd('h", -8, [MyTimeField])

 

[aireq]

Ahh ok that helps a lot. Here's what I've done.

DATE_UTC:
DateSerial(Left([RAW_DATA_UNION]![time_stamp],4),Mid([RAW_DATA_UNION]![time_stamp],6,2),Mid([RAW_DATA_UNION]![time_stamp],9,2))

TIME_UTC:
TimeSerial(Mid([RAW_DATA_UNION]![time_stamp],12,2),Mid([RAW_DATA_UNION]![time_stamp],15,2),Mid([RAW_DATA_UNION]![time_stamp],18,2))

DATETIME_UTC: [DATE_UTC] & " " & [TIME_UTC]

DATETIME_PAC: DateAdd("h",-8,DATETIME_UTC)



Dropping the milliseconds is not a problem. DATETIME_PAC shows the correct
Pacific date and time, except when the UTC timestamp is 8:00 AM. Then it only
shows the date and not the time. Is there a way to force it to show a time of
0:00:00 in this case?

Eric Anastas

The built-in DateAdd function should take care of that for you:

DateAdd('h", -8, [MyTimeField])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a source of data that provides a set of date and time values in UTC
in
the following format:

2008-07-01 06:35:00.0

Yet I need to convert this to Pacific time (-8 hours). I can't just
substract 8 hours because if the time is 8:00 AM UTC or earlier then the
conversion needs to know to go back one day as well.

Is there an easy way to do this? It doesn't seem that access has a
combined
date and time functions.


Eric

 

[Jerry Whittle]

To show the time, use the format options in the form or report.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Ahh ok that helps a lot. Here's what I've done.

DATE_UTC:
DateSerial(Left([RAW_DATA_UNION]![time_stamp],4),Mid([RAW_DATA_UNION]![time_stamp],6,2),Mid([RAW_DATA_UNION]![time_stamp],9,2))

TIME_UTC:
TimeSerial(Mid([RAW_DATA_UNION]![time_stamp],12,2),Mid([RAW_DATA_UNION]![time_stamp],15,2),Mid([RAW_DATA_UNION]![time_stamp],18,2))

DATETIME_UTC: [DATE_UTC] & " " & [TIME_UTC]

DATETIME_PAC: DateAdd("h",-8,DATETIME_UTC)



Dropping the milliseconds is not a problem. DATETIME_PAC shows the correct
Pacific date and time, except when the UTC timestamp is 8:00 AM. Then it only
shows the date and not the time. Is there a way to force it to show a time of
0:00:00 in this case?

Eric Anastas

The built-in DateAdd function should take care of that for you:

DateAdd('h", -8, [MyTimeField])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a source of data that provides a set of date and time values in UTC
in
the following format:

2008-07-01 06:35:00.0

Yet I need to convert this to Pacific time (-8 hours). I can't just
substract 8 hours because if the time is 8:00 AM UTC or earlier then the
conversion needs to know to go back one day as well.

Is there an easy way to do this? It doesn't seem that access has a
combined
date and time functions.


Eric

 

[Douglas J. Steele]

Shouldn't that be DateAdd("h",-8,#2008-07-01 06:35:00#) or
DateAdd("h",-8,CDate("2008-07-01 06:35:00")) Jerry?

As to Daylight Savings Time changes, see
http://www.mvps.org/access/api/api0024.htm at "The Access Web"

 

[Jerry Whittle]

Hi Doug,

Suprisingly it works as a string in the Immediate window for at least this
little test.

Debug.Print DateAdd("h",-8,Left("2008-07-01 06:35:00.0",19))

I was assuming that it had to be a text field. I tried an IsDate on it with
the .0 suffix and it returned false. DateAdd must be smart enough to figure
out the date from a string variant. Here's what Help says:

Date Required. Variant (Date) or literal representing date to which the
interval is added.

A date literal would need #'s around it.

 

[Douglas J. Steele]

Access bends over backwards trying to be accomodating, doesn't it? <g>

That's implicit data coercion. I'm not sure whether it respects regional
settings. In this case, it doesn't matter, but if it were 01/02/2008 would
that be 02 Jan or 01 Feb?