CD********@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.
--