date/time field converting UTC to default timezone

[Laoballer]

I have a table that has a date/time field. I'm going to be importing
date/time field that is in UTC format. When the data gets imported my
date/time data gets converted to a 12 hr AM PM time stamp. How do I
keep this from happening? I want to keep my date/time data as UTC

Thanks,

 

[KARL DEWEY]

Access stores DateTime as a decimal number with fraction of day in the
decimal fraction part.
Just select the format you want to display - query, form. or report.

 

[David W. Fenton]

m:

I have a table that has a date/time field. I'm going to be
importing date/time field that is in UTC format. When the data
gets imported my date/time data gets converted to a 12 hr AM PM
time stamp. How do I keep this from happening? I want to keep my
date/time data as UTC

No, it's not getting converted -- it's only being formatted for
display with AM/PM.

Jet date values are stored as an Integer value for the day (since
12/30/1899), and a decimal value for the time of day (.5 would be
noon). You can see this by typing a few values in the immediate
window.

?Format(0,"m/d/yyyy")
12/30/1899

?Format(.5,"m/d/yyyy hh:nn:ss AM/PM")
12/30/1899 12:00:00 PM

?Format(.7,"m/d/yyyy hh:nn:ss AM/PM")
12/30/1899 04:48:00 PM

?Format(.7,"m/d/yyyy hh:nn:ss")
12/30/1899 16:48:00

Thus, in the controls on your forms or reports, you need to set the
format property of your date/time fields to some variation on:

m/d/yyyy hh:nn:ss
m/d/yyyy h:nn:ss
mm/dd/yyyy hh:nn:ss

None of the named formats support 24-hour time, so you have to
create your own.

Another alternative would be to set the format property
appropriately in the table design. This will be inherited in all the
queries/forms/reports you use that table in.

 

[David W. Fenton]

m:

I have a table that has a date/time field. I'm going to be
importing date/time field that is in UTC format. When the data
gets imported my date/time data gets converted to a 12 hr AM PM
time stamp. How do I keep this from happening? I want to keep my
date/time data as UTC

I answered your question about displaying 24-hour time instead of
AM/PM, but I now notice that the subject heading of your original
post says "converting UTC to default timezone". I'm not sure what
you mean by this. Do you mean you want Greenwich time instead of
local time? Or you want to convert Greenwich time to local time?

I'm not sure how you'd do that, but if I found I needed to, my
strategy would be to try to find a Windows API call that would give
me the timezone setting from the local copy of Windows' regional
settings. That has to be possible, but I'd have to do research to
figure out how.

(I'll do the research if that's what you actually need...)

 

[Laoballer]

I answered your question about displaying 24-hour time instead of
AM/PM, but I now notice that the subject heading of your original
post says "converting UTC to default timezone". I'm not sure what
you mean by this. Do you mean you want Greenwich time instead of
local time? Or you want to convert Greenwich time to local time?

I'm not sure how you'd do that, but if I found I needed to, my
strategy would be to try to find a Windows API call that would give
me the timezone setting from the local copy of Windows' regional
settings. That has to be possible, but I'd have to do research to
figure out how.

(I'll do the research if that's what you actually need...)

sorry, I wasn't very clear with my question. You answered the
question the first time. I have date/time data that is in UTC, and
when I import it the data gets converted to local timezone. Your
first post solved the problem. Thank you.

Thanks,