I want to combine a date field and a time field into 1 field.

  • Thread starter Thread starter Bob Quintal
  • Start date Start date
B

Bob Quintal

I have a field with a date 7/11/2007, and a field with time
00:15:18. I want to combine them into 1 field, 7/11/2007 0:15.

How do I do this in Access or Excel?
Click to expand...
This is an Access group, so my reply will talk only of that.

Access stores date/time fields as a double precision number, days
since December 31, 1899 dot time as a percentage of a day, e.g. noon
is .5, 9:00 pm is .75

So if the two fields are date/time fields, you can just add them.
However, depending on how the data was entered, the date may already
be in the time field, just hidden and/or the time may be in the date
field.

To check, examine each field with format([field name], "General
Date")

If the fields are not date/time type, but text, you will need to
parse out the relevant portions of each string with the mid()
function and reassemble them using the dateserial()and timeserial()
functions
 
I have a field with a date 7/11/2007, and a field with time 00:15:18. I want
to combine them into 1 field, 7/11/2007 0:15.

How do I do this in Access or Excel?
 
Add the two fields' values together in a query.

SELECT DateField, TimeField,
DateField + TimeField AS DateTimeValue
FROM Tablename;
 
In
Tami said:
I have a field with a date 7/11/2007, and a field with time 00:15:18.
I want to combine them into 1 field, 7/11/2007 0:15.

How do I do this in Access or Excel?
Click to expand...

In Access, a date/time field always contains both a date and time. The
time part may be zero (indicating the midnight that begins that date)
but it's always there. Because of the way Access stores date/time info
in this type of field, if you have one field that contains only a date,
and another field that contains only a time, you can combine them by
simply adding the two fields together.

If your fields are really of the date/time data type, you can create a
calculated field in a query that adds the two fields together; for
example, SQL like this:

SELECT DateField + TimeField AS DateTimeField
FROM YourTable;

If you want to actually replace the two separate fields with another,
combined field, you can use an update query to update one of them;
e.g.,

UPDATE YourTable SET DateField = DateField + TimeField;

You'd better make sure you have a backup of the table and/or whole
database before doing this, in case you make a mistake and have to get
back to where you were.
 
DateTime values in Access are implemented as a 64 bit floating point number
as an offset from 30 December 1899 00:00:00, with the integer part
representing the days and the fractional part the times of day. From this it
follows that there is in fact no such thing as a date value or a time value
per se. A date entered without a time has a fractional part of .zero, i.e.
its midnight at the start of the day; a time entered without a date has an
integer part of zero, i.e. it the time on 30 December 1899, Access's day-zero.

Because of the way the values are implemented it follows that if you have a
value with a .zero time of day( a date) and a value with a zero day (a time
of day), simply adding the two together gives you a datetime value of the
time of day on the date in question. So in a query you can return the
combined value with:

SELECT [DateField]+[TimeField] As [DateTimeField]
FROM [YourTable];

If you create a third field to hold the combined values so that you can then
delete the original two fields a simple update query will fill the new field:

UPDATE [YourTable]
Set [NewDateTimeField] = [DateField]+[TimeField];

Before doing this, however, it would be prudent to make sure that all the
rows in the table do in fact contain dates with .zero times of day and times
with zero dates, and that you are not just seeing them formatted as such.
You can do this with an update query which calls the DateValue and TimeValue
functions like so:

UPDATE YourTable
SET [DateField] = DATEVALUE([DateField]),
[TimeField] = TIMEVALUE([TimeField]);

Ken Sheridan
Stafford, England
 
I'll add that if the date is negative (like the 18th century) then you need to subtract the time fraction from the date, not add it.
 
Back
Top