[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

SQL - Rounding datetime to the nearest hour

Hi,

How would I round myu field "ClockTime" to the nearest hour?

So if I had 08:12:34, it must be rounded to 08:00:00
0
NerishaB
Asked:
NerishaB
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
dateadd(second, - datepart(second, ClockTime),  dateadd(minute, - datepart(minute, ClockTime), ClockTime) )
0
 
NerishaBAuthor Commented:
is there a way to do this without creating a function?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
mayank_joshiCommented:
YOU MAY TRY:-

SELECT   DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()), 0)

Open in new window



REPLACE GETDATE() HERE WITH YOUR DATETIME.
0
 
mayank_joshiCommented:
E.G:-
SELECT     DATEADD(Hour, DATEDIFF(Hour, 0, '08:12:34'), 0)

Open in new window

0
 
mayank_joshiCommented:
further if you only want the time part:-
SELECT     RIGHT(DATEADD(Hour, DATEDIFF(Hour, 0, '08:12:34'), 0), 7)

Open in new window

0
 
NerishaBAuthor Commented:
Thanks
0
 
mayank_joshiCommented:
another method for getting only time:-

select CONVERT(varchar(8),DATEADD(Hour, DATEDIFF(Hour, 0, '08:12:34'), 0),108)

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now