Convert Date and Time

[Scott]

I have a field that stores the date and time from a GPS device in a single
field. I want to use a query to split the single field into two separate
fields and then format the date and time fields. See sample data below.

Date & Time Field, New Date, New Time
2008-05-24T15:34:03Z, 5/24/2008, 9:34 AM
2008-05-22T23:51:34Z, 5/22/2008, 5:51 PM
2008-05-21T10:17:49Z, 5/21/2008, 4:17 AM
2008-05-17T13:09:14Z, 5/17/2008, 7:09 AM
2008-05-14T11:43:44Z, 5/14/2008, 5:43 PM

How do I convert the "Date & Time Field" data to "New Date" and "New Time"
data?

Thank you,
Scott

 

[Ken Snell \(MVP\)]

An expression such as this will extract the date and time part from the
original value:

CDate(Replace(Trim(Mid(OriginalFieldName, InStr(OriginalFieldName, ",") +
1)), ",", ""))

You then can extract just the date or the time from the above this way:

Get the date:
DateValue(CDate(Replace(Trim(Mid(OriginalFieldName, InStr(OriginalFieldName,
",") + 1)), ",", "")))

Get the time:
TimeValue(CDate((Replace(Trim(Mid(OriginalFieldName,
InStr(OriginalFieldName, ",") + 1)), ",", ""))

 

[Klatuu]

I would advise against doing so. Unless you use some data type other than
date/time for the time field, it will always contain a date component and
make evaluating the time more difficult. The better practice is to store
date and time in one date/time data type field.

If you need to retrieve only the date portion, you use the DateValue()
function and for the time portion, use the TimeValue() function.

 

[Scott]

How do I format the Date & Time Field sample data using the DateValue() and
TimeValue() functions to display in MM/DD/YYYY HH:MM AM/PM?

Thanks,
Scott

 

[Douglas J. Steele]

In the absence of any formatting information, the results of the DateValue
and TimeValue functions will pick up whatever's been defined as your Short
Date and Short Time formats through Regional Settings.

To force a specific format, you can wrap the Format function around the
function calls:

Format(DateValue([DateAndTimeField]), "mm/dd/yyyy")
Format(TimeValue([DateAndTimeField]), "hh:nn AM/PM")

 

[Scott]

The CDate(Replace(Trim(Mid(OriginalFieldName, InStr(OriginalFieldName, ",") +
1)), ",", "")) expression returns "#Error" for the value. The
OriginalFieldName is ID so my expression in the query is Expr2:
CDate(Replace(Trim(Mid([id],InStr([id],",")+1)),",","")).

Thanks,
Scott

 

[Scott]

I keep getting #Error returned as the field value when I run the query. I'm
converting "2008-05-24T15:34:03Z" to the date "5/24/2008", and the time "9:34
AM" (I don't know how 15:34:03 converts to 9:34 AM, I'm hoping you do).
Should DateValue and TimeValue recognize the field even with the "T"
separating the date and time and the "Z" at the end of the time? Here is my
exact expression where "ID" is the DateAndTimeField. Expr3:
Format(DateValue([id]),"mm/dd/yyyy").

What am I doing wrong?
Scott

In the absence of any formatting information, the results of the DateValue
and TimeValue functions will pick up whatever's been defined as your Short
Date and Short Time formats through Regional Settings.

To force a specific format, you can wrap the Format function around the
function calls:

Format(DateValue([DateAndTimeField]), "mm/dd/yyyy")
Format(TimeValue([DateAndTimeField]), "hh:nn AM/PM")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How do I format the Date & Time Field sample data using the DateValue()
and
TimeValue() functions to display in MM/DD/YYYY HH:MM AM/PM?

Thanks,
Scott

 

[Douglas J. Steele]

There are issues with the T and the Z in that time.

You could use

CDate(Replace(Replace([id], "T", " "), "Z", "")))

to convert it to a Date/Time.

Presumably 15:34:03 converts to 9:34:03 local time because 15:34:03 is Zulu
time (Greenwich Mean Time), and you're located in a time zone that's 6 hours
behind GMT.

Note, by the way, that you do NOT have to use Format when you're converting
(unless you're trying to convert to text fields). How a date/time is
displayed has nothing to do with how it's stored. Date/Time values are
eight-byte floating point numbers, 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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I keep getting #Error returned as the field value when I run the query.
I'm
converting "2008-05-24T15:34:03Z" to the date "5/24/2008", and the time
"9:34
AM" (I don't know how 15:34:03 converts to 9:34 AM, I'm hoping you do).
Should DateValue and TimeValue recognize the field even with the "T"
separating the date and time and the "Z" at the end of the time? Here is
my
exact expression where "ID" is the DateAndTimeField. Expr3:
Format(DateValue([id]),"mm/dd/yyyy").

What am I doing wrong?
Scott

In the absence of any formatting information, the results of the
DateValue
and TimeValue functions will pick up whatever's been defined as your
Short
Date and Short Time formats through Regional Settings.

To force a specific format, you can wrap the Format function around the
function calls:

Format(DateValue([DateAndTimeField]), "mm/dd/yyyy")
Format(TimeValue([DateAndTimeField]), "hh:nn AM/PM")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How do I format the Date & Time Field sample data using the DateValue()
and
TimeValue() functions to display in MM/DD/YYYY HH:MM AM/PM?

Thanks,
Scott

:

I would advise against doing so. Unless you use some data type other
than
date/time for the time field, it will always contain a date component
and
make evaluating the time more difficult. The better practice is to
store
date and time in one date/time data type field.

If you need to retrieve only the date portion, you use the DateValue()
function and for the time portion, use the TimeValue() function.

--
Dave Hargis, Microsoft Access MVP


:

I have a field that stores the date and time from a GPS device in a
single
field. I want to use a query to split the single field into two
separate
fields and then format the date and time fields. See sample data
below.

Date & Time Field, New Date, New Time
2008-05-24T15:34:03Z, 5/24/2008, 9:34 AM
2008-05-22T23:51:34Z, 5/22/2008, 5:51 PM
2008-05-21T10:17:49Z, 5/21/2008, 4:17 AM
2008-05-17T13:09:14Z, 5/17/2008, 7:09 AM
2008-05-14T11:43:44Z, 5/14/2008, 5:43 PM

How do I convert the "Date & Time Field" data to "New Date" and "New
Time"
data?

Thank you,
Scott

 

[Scott]

That worked great, thank you!

Scott

There are issues with the T and the Z in that time.

You could use

CDate(Replace(Replace([id], "T", " "), "Z", "")))

to convert it to a Date/Time.

Presumably 15:34:03 converts to 9:34:03 local time because 15:34:03 is Zulu
time (Greenwich Mean Time), and you're located in a time zone that's 6 hours
behind GMT.

Note, by the way, that you do NOT have to use Format when you're converting
(unless you're trying to convert to text fields). How a date/time is
displayed has nothing to do with how it's stored. Date/Time values are
eight-byte floating point numbers, 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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I keep getting #Error returned as the field value when I run the query.
I'm
converting "2008-05-24T15:34:03Z" to the date "5/24/2008", and the time
"9:34
AM" (I don't know how 15:34:03 converts to 9:34 AM, I'm hoping you do).
Should DateValue and TimeValue recognize the field even with the "T"
separating the date and time and the "Z" at the end of the time? Here is
my
exact expression where "ID" is the DateAndTimeField. Expr3:
Format(DateValue([id]),"mm/dd/yyyy").

What am I doing wrong?
Scott

In the absence of any formatting information, the results of the
DateValue
and TimeValue functions will pick up whatever's been defined as your
Short
Date and Short Time formats through Regional Settings.

To force a specific format, you can wrap the Format function around the
function calls:

Format(DateValue([DateAndTimeField]), "mm/dd/yyyy")
Format(TimeValue([DateAndTimeField]), "hh:nn AM/PM")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


How do I format the Date & Time Field sample data using the DateValue()
and
TimeValue() functions to display in MM/DD/YYYY HH:MM AM/PM?

Thanks,
Scott

:

I would advise against doing so. Unless you use some data type other
than
date/time for the time field, it will always contain a date component
and
make evaluating the time more difficult. The better practice is to
store
date and time in one date/time data type field.

If you need to retrieve only the date portion, you use the DateValue()
function and for the time portion, use the TimeValue() function.

--
Dave Hargis, Microsoft Access MVP


:

I have a field that stores the date and time from a GPS device in a
single
field. I want to use a query to split the single field into two
separate
fields and then format the date and time fields. See sample data
below.

Date & Time Field, New Date, New Time
2008-05-24T15:34:03Z, 5/24/2008, 9:34 AM
2008-05-22T23:51:34Z, 5/22/2008, 5:51 PM
2008-05-21T10:17:49Z, 5/21/2008, 4:17 AM
2008-05-17T13:09:14Z, 5/17/2008, 7:09 AM
2008-05-14T11:43:44Z, 5/14/2008, 5:43 PM

How do I convert the "Date & Time Field" data to "New Date" and "New
Time"
data?

Thank you,
Scott

 

[Ken Snell \(MVP\)]

My apology -- I apparently misunderstood your data structure. My answer was
based on a field that contained this single value:

2008-05-24T15:34:03Z, 5/24/2008, 9:34 AM