Append to date & time

  • Thread starter Thread starter Bunky
  • Start date Start date
B

Bunky

I have a date field that currently has only the month, day and year. I would
like to add the time to it as well. The input to the append does not have
the time. Can I format a time and have it as part of the date field?

Thanks
 
Bunky said:
I have a date field that currently has only the month, day and year. I
would
like to add the time to it as well. The input to the append does not have
the time. Can I format a time and have it as part of the date field?


I'm not entirely sure what you have to work with here. It may help to know
that both date and time values are stored in the same specialized
floating-point format -- date values as the integer part, time values as the
fractional part -- so for dates since December 30, 1899 (the "zero" date in
this structure), you can just add time values to date values to get
date/time values. I'm not sure if this works with dates before the zero
date.

Lets suppose that you have two tables, one with the date values and one with
the time values, which share a primary key. Further suppose that both the
date fields and the time fields are of the date/time field type. For
example, suppose you have these tables:

MyDateTable
-------------------
ID (primary key)
DateField (type Date, currently contains only dates)

MyTimeTable
-------------------
ID (primary key, fk to MyDate.ID)
TimeField (type Date, currently contains only times)

Further suppose that all these dates are since 12/30/1899. Then you could
update the date fields in MyDateTable to include the times as well using a
query like this:

UPDATE
MyDateTable INNER JOIN MyTimeTable
ON MyDateTable.ID = MyTimeTable.ID
SET MyDateTable.DateField =
MyDateTable.DateField + MyTimeTable.TimeField

I haven't tested it, but something along those lines ought to work.
 
Hi

Before you do anything try opening the table in design and change the format
of the field to General Date.
You may alrady have the details you need.
 
I've tried this but if the input does not have the date, it does not put a
date in during the append.
 
What I am working with is one table that has a general date defined in a
date/time field. This table has a date in the field - no time. When I
append this data to another table, I would like to have it include a time
with this date. Is this possible? If so How????
 
You can use an append with dateadd (if you have the date) - but it would seem
(to me that unless (as Dirk says) you actually have the time in a table - how
will you know what time to add.

Sorry I'm not understand this
 
I would like to just slug a time in (12:00:00 AM) comes to mind. What would
be the syntax to do that with the date that is a good date?
 
Bunky said:
I would like to just slug a time in (12:00:00 AM) comes to mind. What
would
be the syntax to do that with the date that is a good date?


Bunky, *every* date field has a time portion that is part of it. If that
time was never set, the time is 12:00:00 AM. It purely a matter of the
format applied to the field that determines whether you see it when you open
the table or not. By default, a date a time of 12:00:00 AM, displayed using
the "general date" format, will not show the time, because it assumes that
you don't care about it. If you want to show the time always, even if it's
12:00 AM, then use a format such as

m/d/yyyy h:nn:ss AMPM
 
I'm still lost with this but .....

You could use something like this to hange a field based on the date
contained within the table

UPDATE SomeTable SET SomeTable.NewTimeField =
Format(SomeTable!DateField,"General Date")
WHERE ((Not (SomeTable.DateField) Is Null));

Change the names
Note - I assume you have a date/time field called DateField and you want to
insert this information into a field called NewTimeField formated GeneralDate
(this will give you the 00:00am you want. The table is called SomeTable

Good luck
 
Dick,

Thank you so much! I did not know the time is there always. It solves my
problem totally.

Kent
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top