By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,804 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,804 IT Pros & Developers. It's quick & easy.

date/time

P: n/a
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

Oct 5 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

brino wrote:
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
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]

Oct 5 '06 #2

P: n/a
brino wrote:
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
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
CD********@FortuneJames.com

Mr. Bojangles on online karaoke (not me):
http://www.ksolo.com/actions/showSon...2425&uid=15273

Oct 6 '06 #3

P: n/a

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.

--

Oct 6 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.