date/time

[brino]

hi all!

i need to combine 2 fields into one.
one field is a Date field and the other is a Time field.
so i need to have these combined into a Date/Time field so that i can
do some hours calculations in a query.can i do this with code in my
input form ???

thanks
brino

[pietl...@hotmail.com]

you should be able to add the two together. The integer part is the
date, and the fractional part is the time. You could create an unbound
control on your form and set its controlsource to [ctlDate] + [ctlTime]

[CDMAP...@fortunejames.com]

Greetings brino,

Here's one way to do it:

MyTable
theDate Date/Time m/d/yyyy
theTime Date/Time hh:nn:ss
theDate theTime
10/1/2006 15:15:00
10/2/2006 15:20:01

SELECT DateAdd("s", DateDiff("s", CDate("00:00:00"), theTime), theDate)
AS FullDate FROM MyTable;

FullDate
10/1/2006 3:15:00 PM
10/2/2006 3:20:01 PM

In code:

dtFullDate = DateAdd("s", DateDiff("s", CDate("00:00:00"), dtTime),
dtDate)

James A. Fortune
CDMAP...@FortuneJames.com

Mr. Bojangles on online karaoke (not me):
http://www.ksolo.com/actions/showSongProfile.do?rid=107473&sid=22425&uid=15273

[onedaywhen]

CDMAP...@FortuneJames.com wrote:
> > i need to combine 2 fields into one.
> > one field is a Date field and the other is a Time field.
>

> SELECT DateAdd("s", DateDiff("s", CDate("00:00:00"), theTime), theDate)
> AS FullDate FROM MyTable;

I think this should work OK; maybe handle existing null values? Of
course, this should be a one-off scrubbing exercise before fixing the
design flaw e.g.

ALTER TABLE MyTable ADD
COLUMN effective_date DATETIME DEFAULT NOW() NOT NULL
;
UPDATE MyTable
SET effective_date = IIF(ISDATE(theTime), DATEADD('s', DATEDIFF('s',
CDATE('00:00:00'), CDATE(theTime)), theDate), theDate)
;
ALTER TABLE MyTable DROP
COLUMN theDate
;
ALTER TABLE MyTable DROP
COLUMN theTime
;

Jamie.

--