Add hours, minutes or seconds to DATETIME

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 2008 Forums

Transact-SQL (2008)

Add hours, minutes or seconds to DATETIME

Author

Topic

DeLarcco
Starting Member

4 Posts

Posted - 2011-06-27 : 11:33:10

Hi,

I would like to add time to standard DATETIME (yyyy-MM-dd HH:mm:ss) but in format like (HH:mm:ss).
Example:
2011-06-27 17:28:25 + 02:00:00 = 2011-06-27 19:28:25

What would be de easiest way to do it?

Thanks for help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-27 : 11:40:59

One way

select
dateadd(hour,convert(int,parsename(replace('02:00:00',':','.'),3)),dateadd(minute,convert(int,parsename(replace('02:00:00',':','.'),2)),dateadd(second,convert(int,parsename(replace('02:00:00',':','.'),1)),'2011-06-27T17:28:25')))

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-27 : 11:46:02

I don't have 2008, so this may not be applicable

declare @dt DATETIME
declare @hhmmss varchar(20)

set @dt = '2011-06-27 17:28:25'
set @hhmmss = '02:00:00'
select dateadd(s,datediff(s,0,convert(datetime,@hhmmss)),@dt)

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-27 : 11:47:11

Since you are on SQL 2008 the TIME data type could be used:


DECLARE @d DATETIME, @t TIME;
set @d = '2011-06-27 17:28:25';
SET @t = '02:00:00';
SELECT @d + @t;

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-06-27 : 17:09:09

You can just convert them to datatime values (if they aren't already) and add them together:


select
	DatesAdded = a.DT1+a.DT2,
	a.*
from
	( -- Test Data
	select
		DT1 = convert(datetime,'2011-06-27 17:28:25'),
		DT2 = convert(datetime,'02:00:00')
	) a

Results:

DatesAdded               DT1                      DT2 
-----------------------  -----------------------  -----------------------
2011-06-27 19:28:25.000  2011-06-27 17:28:25.000  1900-01-01 02:00:00.000

CODO ERGO SUM

DeLarcco
Starting Member

4 Posts

Posted - 2011-06-28 : 14:17:20

Thank you all very much. This was very helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources