Adding dates

[john0532]

I've got 2 date/time fields I want to combine/add and put in another date/time field in another table. Here's an example of the 2 fields:
[arrivetime] [WaitingPeriod]
9/16/2002 11:49:49 PM 1:29:00 AM

Logically if you add them you would get:
9/17/2002 1:18:49 AM
But I'm getting:
9/15/2002 1:18:49 AM

Everything appears to be correct except it subtracted a day instead of adding a day. Here's the query I used:

INSERT INTO archive ( InProgress )
SELECT [WaitingPeriod]+[arrivetime] AS InProgress
FROM archive_old
WHERE (((ArriveTime)<'9/17/2002'));

thanks

 

[JayKusch]

I do not understand how you are adding these dates????

Explain what you are trying to accomplish here?

Thanks

J. Kusch

 

[nikhilparchure]

I have actually tried the example and results seem to be coming fine...
pls see if your queries as comparable with mine's

Or just run the below script in your database and tell us the results.


Create table TimeAdd

(
TestDate datetime ,
TestTime datetime
)

Insert into TimeAdd values ( '9/16/2002 11:49:49 PM' , '1:29:00 AM' )

Select * from TimeAdd
Go
Select TestDate + TestTime from TimeAdd
Go
SELECT [TestDate]+[TestTime] AS InProgress
FROM TimeAdd
WHERE (((TestDate)<'9/17/2002'));
Go

TestDate TestTime
2002-09-16 23:49:49.000 1900-01-01 01:29:00.000

(1 row(s) affected)

Summed Values ------------------------------------------------------
2002-09-17 01:18:49.000

(1 row(s) affected)

InProgress (As Per your Query)
------------------------------------------------------
2002-09-17 01:18:49.000

(1 row(s) affected)

 

[john0532]

I tried your query and it worked correct for me, too. I went back to the Enterprise manager and looked at the fields there. I stripped off the
1900-01-01
from TestTime to make it the same as what I'm working with and ran your query again and got
1899-12-30 01:29:00.000
instead of
1900-12-30 01:29:00.000

The table I'm working with was transfered from an Access database a couple years ago so I can only guess what it was origanally. Maybe just a time field?
Thanks nikhilparchure