Add Time from one field to another 1

[briangriffin]

What's the easy way to add the time from column 2 to the date from column 1?

Col 1 Col 2 Needed Result
2010-07-23 00:00:00 1900-01-01 09:41:52 2010-07-23 09:41:52


Thanks in advance...

 

[r937]

UPDATE daTable
SET col1 = DATEADD(SS,DATEDIFF(SS,0,col2),col1)

caution: untested

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[briangriffin]

Perfect, thank you.

 

[djj55]

If you just add the time then maybe something like this:

declare @myt table (col1 datetime, col2 datetime, col3 datetime)
insert into @myt values('7/30/2010', '1900-01-01 09:41:52', null)
select *,  [b]col1 + convert(varchar, col2, 114)[/b]
from @myt

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[simian336]

Mine appears to have cut off the seconds or rounded them...

Any ideas.

Simi


declare @date datetime
declare @time datetime

set @date='2010-07-23 00:00:00'
set @time='1900-01-01 09:41:52'

print DATEDIFF(ss,0,@time)
--34912
print DATEADD(ss,DATEDIFF(ss,0,@time),@date)
--Jul 23 2010 9:41AM

print CONVERT(VARCHAR(26), DATEADD(ss,DATEDIFF(ss,0,@time),@date), 109)
--Jul 23 2010 9:41:52:000AM

print @date + convert(varchar, @time, 114)
--Jul 23 2010 9:41AM