Combining Date/Time

[Welthey]

Currently I have a date and a time field in my table. I need to be able to
combine these two items into one field, but when I try to combine them it is
subtracting 2 days off of the actual date. My table is in SQL. Is there any
way around this, this is the code that I am using

update dbo.purgedrecords
Set [datetime] = AuthDate + convert(datetime, AuthTime)

Previously this was working when the default date on the time was 01/01/1900
and then somehow something changed and the default date is now showing as
12/30/1899. Is there different code that I can use to keep this from
happening in the future?

 

[Arvin Meyer [MVP]]

The default, zero date is 12/30/1899. Time vales are on the decimal side, so
you are string to add:

0.25 to your current date, and the value is being read as:

12/30/1899 6:00 AM

Try converting both values to a string and concatenating the time to the
date, then converting back to a date.

 

[Al Campagna]

Welthey,
Try just AuthDate + AuthTime.
1/1/08 = 39448.00000
12:00 PM = .5000000
-------------------------------
39448.500000 = 1/1/08 12:00 PM

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Douglas J. Steele]

Those dates you quote (01/01/1900 and 12/30/1899) make it sound as though no
value is actually getting assigned to AuthDate (1 Jan, 1900 is the "base
date" in SQL server, while 30 Dec, 1899 is it in Access)

That having been said though, I don't believe it's necessary to use the
Convert function. Dates and times are stored as numeric values: I believe
you can simply add AuthDate + AuthTime.