Date/Time conversion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have a table that has two fields. One field is a Date field,
formatted as a Date. The other field is a time, formatted as an integer. Here
is some sample data from this table:

Date Time
3/21/2006 1000
3/21/2006 1200
3/21/2006 1430

I need to create a third field to have the Date/Time formatted as a
Date/Time. For example, with the data above, a third field would read:

3/21/2006 10:00 AM
3/21/2006 12:00 PM
3/21/2006 2:30 PM

I would like to just create a query and create the third field in there. Any
ideas how to do this? I'm at wits end. Thanks!!!

Nick
 
Nick,

There may be more elegant ways to do this than what I'm suggesting, but
this ought to work.

I added a record to your Table, to give a bit of variety in the date.

[DateStuff] Table Datasheet View:

Date Time
---------- -----
3/20/2006 1923
3/21/2006 1430
3/21/2006 1200
3/21/2006 1000

In the Query, since Access stores dates by converting them to some
number of days, I split out the hour and minute portions of your [Time]
field and converted them to fractions of a day.

[Q_FullDateTime]

SELECT DS.Date, DS.Time,
[DS]![Time]\100 AS Hours,
[DS]![Time] Mod 100 AS Mins,
[DS]![Date]+([Hours]+[Mins]/60)/24 AS Combined
FROM DateStuff AS DS
ORDER BY DS.Date, [DS]![Time]\100,
[DS]![Time] Mod 100;

The results look like this, if the last field is formatted as "General
Date", but you can choose other formats, or use the Format$() function
to convert the date to a string.

[Q_FullDateTime]

Date Time Hours Mins Combined
--------- ---- ----- ---- ---------------------
3/20/2006 1923 19 23 3/20/2006 7:23:00 PM
3/21/2006 1000 10 0 3/21/2006 10:00:00 AM
3/21/2006 1200 12 0 3/21/2006 12:00:00 PM
3/21/2006 1430 14 30 3/21/2006 2:30:00 PM

BTW, when you said "I need to create a third field...", I do hope that
you don't intend to actually store that third field into your Table, in
addition to the fields already there. Storing information that you can
easily calculate is wasteful and can cause maintenance problems. If you
store the result in your Table and remove the other fields (which you
could easily recover from a Date/Time field), that might be a good thing
to do, unless you expect to continue receiving data in your current
format from some other source.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
An expression like this should convert it:
DateTimeField:[DateField]+TimeValue(Format([IntTimeField],"00:00"))
 
Back
Top