Difference of two datetime columns caused overflow

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Difference of two datetime columns caused overflow

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-14 : 13:19:00

Difference of two datetime columns caused overflow at runtime.

dateadd(ss,ISNULL(datediff(ss,0,starttime),0),startdate) AS mDateTime

INSERT INTO [TargetDB].[dbo].[tData](
tName,
tID,
tDateTimeStart,
tDateTimeStop)
SELECT
T.tName,
T.tID,
tStart = dateadd(ss,ISNULL(datediff(ss,0,t.starttime),0),t.startdate),
tStop = dateadd(ss,ISNULL(datediff(ss,0,t.stoptime),0),t.stopdate)
FROM
INSERTED T

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 13:33:02

This is what is causing your error..

datediff(ss,0,launchtime)

Maybe you just need this..(if you are trying to find the diff in seconds between launchtime and launchdate)

datediff(ss,launchtime,launchdate)

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-14 : 13:40:24

select dateadd(ss,ISNULL(datediff(ss,0,starttime),0),startdate) AS mDateTime from tTable
This works. however the first posted code is not working. keep getting the same error.

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-14 : 14:00:41

start date and start time in separate table. I am trying put the date and time in one column (datetime column of the target table).

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-14 : 14:32:21

INSERT INTO [TargetDB].[dbo].[tData](
tName,
tID,
tDateTimeStart,
tDateTimeStop)
SELECT
T.tName,
T.tID,
tStart = CONVERT(datetime, convert(varchar(10), StartDate, 101 ) + ' ' + convert(varchar(10), StartTime, 108 )),
tStop = CONVERT(datetime, convert( varchar(10), StopDate, 101 ) + ' ' + convert(varchar(10), StopTime, 108 ))
FROM
INSERTED T

This worked....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources