Get DateTime calculated value - SQL Server Forums

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)

Get DateTime calculated value

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-11-08 : 09:20:56

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 - 2013-11-08 : 10:16:35

--- *** 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;

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-11-08 : 10:21:02

How about something like:


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

djj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-08 : 10:48:32

dateadd(minute, 45, dateadd(hour, datediff(hour, 0, <yourColumnOrVariable>)+1, 0))

Be One with the Optimizer
TG

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-11-08 : 11:13:52

[code]
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;
[/code]

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-11-08 : 14:32:41

The Ifor solution is the only that get the desidered result.
Thanks a lot.

Luis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources