SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Get DateTime calculated value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 11/08/2013 :  09:20:56  Show Profile  Reply with Quote
Hello all,
I need to write a statement that returns me
a datetime value that is the following hour (with 00 for minutes and seconds)
from the current datetime plus 45min.

For example, if now is:

12:09:00

I add 45min -> 12:53:00

then I have to get -> 13:00:00


If now is 12:17:00

I add 45min -> 13:02:00

then I have to get -> 14:00:00

Can anyone help me?

Thanks in advance.


Luis

Ifor
Aged Yak Warrior

590 Posts

Posted - 11/08/2013 :  10:16:35  Show Profile  Reply with Quote
--- *** Test Data ***
DECLARE @t TABLE
(
TestTime datetime NOT NULL
);
INSERT INTO @t
VALUES ('20131108 12:09:00')
,('20131108 12:17:00');
--- *** End Test Data ***

SELECT TestTime
,DATEADD(hour, DATEPART(hour,DATEADD(minute, 45, TestTime)) + 1, DATEADD(day, DATEDIFF(day, 0, TestTime), 0)) As NewTime
FROM @t;
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 11/08/2013 :  10:21:02  Show Profile  Reply with Quote
How about something like:

SELECT CONVERT(VARCHAR(2), (DATEADD(MINUTE, 45, GETDATE())) + 1, 114)


djj
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/08/2013 :  10:48:32  Show Profile  Reply with Quote
dateadd(minute, 45, dateadd(hour, datediff(hour, 0, <yourColumnOrVariable>)+1, 0))

Be One with the Optimizer
TG
Go to Top of Page

Ifor
Aged Yak Warrior

590 Posts

Posted - 11/08/2013 :  11:13:52  Show Profile  Reply with Quote

DECLARE @t TABLE
(
	TestTime datetime NOT NULL
);
INSERT INTO @t
VALUES ('20131108 12:09:00')
	,('20131108 12:17:00');
--- *** End Test Data ***

SELECT TestTime
	,DATEADD(hour, DATEDIFF(hour, 0, DATEADD(minute, 45, TestTime)) + 1 , 0) As NewTime
FROM @t;
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 11/08/2013 :  14:32:41  Show Profile  Reply with Quote
The Ifor solution is the only that get the desidered result.
Thanks a lot.

Luis
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000