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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

700 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
352 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
6065 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

700 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  
 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.05 seconds. Powered By: Snitz Forums 2000