?
Solved

SQL and DateTime calculations

Posted on 2008-02-12
9
Medium Priority
?
4,754 Views
Last Modified: 2010-04-21
Hi,
I'm trying to fill a variable with the current date + 2 hours.
the problem is that I want the minutes and the seconds to be 0.

What I succeeded to do is increasing the date with 2 hours (by DateAdd - see below)
But if the datetime is: 2008/01/13 18:30:19, it will change to 2008/01/13 20:30:19.
I want it to be changed to: 2008/01/13 20:00:00

This code I used so far:

declare @tempDate DateTime
declare @nextDate DateTime
set @tempDate = DATEADD(hour, 2, GetDate())

Thanks
0
Comment
Question by:giligatech
9 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 20873332
try this
declare @tempDate DateTime
declare @nextDate DateTime
set @tempDate = DATEADD(hour, 2, GetDate())
set @tempDate = DATEADD(minute, -minute(@tempDate), @tempDate)
set @tempDate = DATEADD(minute, -second(@tempDate), @tempDate)

Open in new window

0
 

Author Comment

by:giligatech
ID: 20873393
sorry, but it doesn't work.
Msg 195, Level 15, State 10, Line 4
'minute' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Line 5
'second' is not a recognized built-in function name.

(MS SQL Server 2005)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20873398
use DATEPART(minute, @tempdate) instead of minute(@tempdate) (same for second)
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
LVL 7

Accepted Solution

by:
Chandan_Gowda earned 500 total points
ID: 20873409

declare @tempDate DateTime
declare @nextDate DateTime
set @tempDate = DATEADD(hour, 2, GetDate())
set @tempDate = DATEADD(minute, -datepart(minute,(@tempDate)), @tempDate)
set @tempDate = DATEADD(second, -datepart(second,@tempDate), @tempDate)
SELECT @tempDate
0
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 20873441
---------Try this........
declare @tempDate DateTime
declare @nextDate DateTime
set @tempDate = DATEADD(hour, 2, GetDate())
set @tempDate = DATEADD(minute, -datepart(minute,(@tempDate)), @tempDate)
set @tempDate = DATEADD(second, -datepart(second,@tempDate), @tempDate)
set @tempDate = DATEADD(second, -datepart(second,@tempDate), @tempDate)
SELECT CONVERT(VARCHAR,@tempDate,20)
0
 

Author Closing Comment

by:giligatech
ID: 31430082
thank you
0
 
LVL 52

Expert Comment

by:Mark Wills
ID: 21203633
For what it is worth...

select dateadd(hh,datepart(hh,getdate())+2,convert(varchar,getdate(),106))
1
 

Expert Comment

by:esmoreno
ID: 21338138
Mark Wills' solution is not only the most elegant - but it goes even farther by ensuring that the milliseconds is zero as well. Thanks Mark!
0
 
LVL 15

Expert Comment

by:MohammedU
ID: 21416760
select  convert(varchar(13),dateadd(hh,2,getdate()),121)+':00:00.000'
OR
select  cast (convert(varchar(13),dateadd(hh,2,getdate()),121)+':00:00.000' as Datetime)
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

594 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question