Round datetime to the beginning of the current hour
I would like to round a datetime (should also return a datetime) to the beginning of the hour it's on, so for example:
2018-09-22 11:31:42.411 -> 2018-09-22 11:00:00.000
2018-09-22 11:08:02.456 -> 2018-09-22 11:00:00.000
2018-09-22 11:52:08.005 -> 2018-09-22 11:00:00.000
Can someone help with this please?
sql
sql-server datetime edited Nov 26 '18 at 12:55
Salman A
185k67345441
asked Nov 26 '18 at 11:10
AzhariAzhari
256
add a comment |
I would like to round a datetime (should also return a datetime) to the beginning of the hour it's on, so for example:
2018-09-22 11:31:42.411 -> 2018-09-22 11:00:00.000
2018-09-22 11:08:02.456 -> 2018-09-22 11:00:00.000
2018-09-22 11:52:08.005 -> 2018-09-22 11:00:00.000
Can someone help with this please?
sql
sql-server datetime edited Nov 26 '18 at 12:55
Salman A
185k67345441
asked Nov 26 '18 at 11:10
AzhariAzhari
256
add a comment |
I would like to round a datetime (should also return a datetime) to the beginning of the hour it's on, so for example:
2018-09-22 11:31:42.411 -> 2018-09-22 11:00:00.000
2018-09-22 11:08:02.456 -> 2018-09-22 11:00:00.000
2018-09-22 11:52:08.005 -> 2018-09-22 11:00:00.000
Can someone help with this please?
sql
sql-server datetime edited Nov 26 '18 at 12:55
Salman A
185k67345441
asked Nov 26 '18 at 11:10
AzhariAzhari
256
I would like to round a datetime (should also return a datetime) to the beginning of the hour it's on, so for example:
2018-09-22 11:31:42.411 -> 2018-09-22 11:00:00.000
2018-09-22 11:08:02.456 -> 2018-09-22 11:00:00.000
2018-09-22 11:52:08.005 -> 2018-09-22 11:00:00.000
Can someone help with this please?
sql
sql-server datetime sql
sql-server datetime edited Nov 26 '18 at 12:55
Salman A
185k67345441
asked Nov 26 '18 at 11:10
AzhariAzhari
256
edited Nov 26 '18 at 12:55
Salman A
185k67345441
asked Nov 26 '18 at 11:10
AzhariAzhari
256
edited Nov 26 '18 at 12:55
Salman A
185k67345441
edited Nov 26 '18 at 12:55
Salman A
185k67345441
edited Nov 26 '18 at 12:55
Salman A
185k67345441
185k67345441
asked Nov 26 '18 at 11:10
AzhariAzhari
256
asked Nov 26 '18 at 11:10
AzhariAzhari
256
asked Nov 26 '18 at 11:10
AzhariAzhari
256
256
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
Assuming the datatype is DATETIME or DATETIME2 you can use this common trick:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, yourdate), 0)
DATEDIFF(HOUR, 0, yourdate) will give you the number of hours (and nothing else) between the zeroth date and yourdate. When you add those hours back to the zeroth date you get yourdate "floored" to the hour.
answered Nov 26 '18 at 11:24
Salman ASalman A
185k67345441
This does exactly what I need it to do, thank you!
– Azhari
Nov 26 '18 at 11:26
add a comment |
Another method is the DATEADD, DATEDIFF:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, '20000101',YourColumn),'20000101') AS RoundedValue
FROM YourTable;
answered Nov 26 '18 at 11:21
LarnuLarnu
22.4k51933
Can you explain what this is doing?
– Azhari
Nov 26 '18 at 11:22
@Azhari It works out the difference in hours between'200-01-01'and your column, and then adds that many hours to'2000-01-01'.
– Larnu
Nov 26 '18 at 11:23
I need it to round to the beginning of the current hour
– Azhari
Nov 26 '18 at 11:24
@Azhari and that's what it does.
– Larnu
Nov 26 '18 at 11:24
1
Small comment, there is a typo in the first date, one 0 missing.
– SQL_M
Nov 26 '18 at 11:28
|
show 3 more comments
You can use DATETIMEFROMPARTS() Function.
select DATETIMEFROMPARTS(YEAR(@date),MONTH(@date),DAY(@date),DATEPART(HOUR,@date),00,00,00)
Here @date is replaced with YourColumn or any variable.
answered Nov 26 '18 at 11:31
Bhargav J PatelBhargav J Patel
1145
add a comment |
This converts the value to a string, takes the left 14 chars and add the 00:00:00 values
select left(convert(varchar , datecol, 121),14) + '00:00:00' as mydatecol from table
answered Nov 26 '18 at 11:36
WilhelmWilhelm
997
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53479857%2fround-datetime-to-the-beginning-of-the-current-hour%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
Assuming the datatype is DATETIME or DATETIME2 you can use this common trick:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, yourdate), 0)
DATEDIFF(HOUR, 0, yourdate) will give you the number of hours (and nothing else) between the zeroth date and yourdate. When you add those hours back to the zeroth date you get yourdate "floored" to the hour.
answered Nov 26 '18 at 11:24
Salman ASalman A
185k67345441
This does exactly what I need it to do, thank you!
– Azhari
Nov 26 '18 at 11:26
add a comment |
Assuming the datatype is DATETIME or DATETIME2 you can use this common trick:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, yourdate), 0)
DATEDIFF(HOUR, 0, yourdate) will give you the number of hours (and nothing else) between the zeroth date and yourdate. When you add those hours back to the zeroth date you get yourdate "floored" to the hour.
answered Nov 26 '18 at 11:24
Salman ASalman A
185k67345441
This does exactly what I need it to do, thank you!
– Azhari
Nov 26 '18 at 11:26
add a comment |
Assuming the datatype is DATETIME or DATETIME2 you can use this common trick:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, yourdate), 0)
DATEDIFF(HOUR, 0, yourdate) will give you the number of hours (and nothing else) between the zeroth date and yourdate. When you add those hours back to the zeroth date you get yourdate "floored" to the hour.
answered Nov 26 '18 at 11:24
Salman ASalman A
185k67345441
Assuming the datatype is DATETIME or DATETIME2 you can use this common trick:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, yourdate), 0)
DATEDIFF(HOUR, 0, yourdate) will give you the number of hours (and nothing else) between the zeroth date and yourdate. When you add those hours back to the zeroth date you get yourdate "floored" to the hour.
answered Nov 26 '18 at 11:24
Salman ASalman A
185k67345441
edited Nov 26 '18 at 11:41
answered Nov 26 '18 at 11:24
Salman ASalman A
185k67345441
answered Nov 26 '18 at 11:24
Salman ASalman A
185k67345441
answered Nov 26 '18 at 11:24
Salman ASalman A
185k67345441
185k67345441
This does exactly what I need it to do, thank you!
– Azhari
Nov 26 '18 at 11:26
add a comment |
This does exactly what I need it to do, thank you!
– Azhari
Nov 26 '18 at 11:26
This does exactly what I need it to do, thank you!
– Azhari
Nov 26 '18 at 11:26
This does exactly what I need it to do, thank you!
– Azhari
Nov 26 '18 at 11:26
add a comment |
Another method is the DATEADD, DATEDIFF:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, '20000101',YourColumn),'20000101') AS RoundedValue
FROM YourTable;
answered Nov 26 '18 at 11:21
LarnuLarnu
22.4k51933
Can you explain what this is doing?
– Azhari
Nov 26 '18 at 11:22
@Azhari It works out the difference in hours between'200-01-01'and your column, and then adds that many hours to'2000-01-01'.
– Larnu
Nov 26 '18 at 11:23
I need it to round to the beginning of the current hour
– Azhari
Nov 26 '18 at 11:24
@Azhari and that's what it does.
– Larnu
Nov 26 '18 at 11:24
1
Small comment, there is a typo in the first date, one 0 missing.
– SQL_M
Nov 26 '18 at 11:28
|
show 3 more comments
Another method is the DATEADD, DATEDIFF:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, '20000101',YourColumn),'20000101') AS RoundedValue
FROM YourTable;
answered Nov 26 '18 at 11:21
LarnuLarnu
22.4k51933
Can you explain what this is doing?
– Azhari
Nov 26 '18 at 11:22
@Azhari It works out the difference in hours between'200-01-01'and your column, and then adds that many hours to'2000-01-01'.
– Larnu
Nov 26 '18 at 11:23
I need it to round to the beginning of the current hour
– Azhari
Nov 26 '18 at 11:24
@Azhari and that's what it does.
– Larnu
Nov 26 '18 at 11:24
1
Small comment, there is a typo in the first date, one 0 missing.
– SQL_M
Nov 26 '18 at 11:28
|
show 3 more comments
Another method is the DATEADD, DATEDIFF:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, '20000101',YourColumn),'20000101') AS RoundedValue
FROM YourTable;
answered Nov 26 '18 at 11:21
LarnuLarnu
22.4k51933
Another method is the DATEADD, DATEDIFF:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, '20000101',YourColumn),'20000101') AS RoundedValue
FROM YourTable;
answered Nov 26 '18 at 11:21
LarnuLarnu
22.4k51933
edited Nov 26 '18 at 11:29
answered Nov 26 '18 at 11:21
LarnuLarnu
22.4k51933
answered Nov 26 '18 at 11:21
LarnuLarnu
22.4k51933
answered Nov 26 '18 at 11:21
LarnuLarnu
22.4k51933
22.4k51933
Can you explain what this is doing?
– Azhari
Nov 26 '18 at 11:22
@Azhari It works out the difference in hours between'200-01-01'and your column, and then adds that many hours to'2000-01-01'.
– Larnu
Nov 26 '18 at 11:23
I need it to round to the beginning of the current hour
– Azhari
Nov 26 '18 at 11:24
@Azhari and that's what it does.
– Larnu
Nov 26 '18 at 11:24
1
Small comment, there is a typo in the first date, one 0 missing.
– SQL_M
Nov 26 '18 at 11:28
|
show 3 more comments
Can you explain what this is doing?
– Azhari
Nov 26 '18 at 11:22
@Azhari It works out the difference in hours between'200-01-01'and your column, and then adds that many hours to'2000-01-01'.
– Larnu
Nov 26 '18 at 11:23
I need it to round to the beginning of the current hour
– Azhari
Nov 26 '18 at 11:24
@Azhari and that's what it does.
– Larnu
Nov 26 '18 at 11:24
1
Small comment, there is a typo in the first date, one 0 missing.
– SQL_M
Nov 26 '18 at 11:28
Can you explain what this is doing?
– Azhari
Nov 26 '18 at 11:22
Can you explain what this is doing?
– Azhari
Nov 26 '18 at 11:22
@Azhari It works out the difference in hours between
'200-01-01' and your column, and then adds that many hours to '2000-01-01'.– Larnu
Nov 26 '18 at 11:23
@Azhari It works out the difference in hours between
'200-01-01' and your column, and then adds that many hours to '2000-01-01'.– Larnu
Nov 26 '18 at 11:23
I need it to round to the beginning of the current hour
– Azhari
Nov 26 '18 at 11:24
I need it to round to the beginning of the current hour
– Azhari
Nov 26 '18 at 11:24
@Azhari and that's what it does.
– Larnu
Nov 26 '18 at 11:24
@Azhari and that's what it does.
– Larnu
Nov 26 '18 at 11:24
1
1
Small comment, there is a typo in the first date, one 0 missing.
– SQL_M
Nov 26 '18 at 11:28
Small comment, there is a typo in the first date, one 0 missing.
– SQL_M
Nov 26 '18 at 11:28
|
show 3 more comments
You can use DATETIMEFROMPARTS() Function.
select DATETIMEFROMPARTS(YEAR(@date),MONTH(@date),DAY(@date),DATEPART(HOUR,@date),00,00,00)
Here @date is replaced with YourColumn or any variable.
answered Nov 26 '18 at 11:31
Bhargav J PatelBhargav J Patel
1145
add a comment |
You can use DATETIMEFROMPARTS() Function.
select DATETIMEFROMPARTS(YEAR(@date),MONTH(@date),DAY(@date),DATEPART(HOUR,@date),00,00,00)
Here @date is replaced with YourColumn or any variable.
answered Nov 26 '18 at 11:31
Bhargav J PatelBhargav J Patel
1145
add a comment |
You can use DATETIMEFROMPARTS() Function.
select DATETIMEFROMPARTS(YEAR(@date),MONTH(@date),DAY(@date),DATEPART(HOUR,@date),00,00,00)
Here @date is replaced with YourColumn or any variable.
answered Nov 26 '18 at 11:31
Bhargav J PatelBhargav J Patel
1145
You can use DATETIMEFROMPARTS() Function.
select DATETIMEFROMPARTS(YEAR(@date),MONTH(@date),DAY(@date),DATEPART(HOUR,@date),00,00,00)
Here @date is replaced with YourColumn or any variable.
answered Nov 26 '18 at 11:31
Bhargav J PatelBhargav J Patel
1145
answered Nov 26 '18 at 11:31
Bhargav J PatelBhargav J Patel
1145
answered Nov 26 '18 at 11:31
Bhargav J PatelBhargav J Patel
1145
answered Nov 26 '18 at 11:31
Bhargav J PatelBhargav J Patel
1145
1145
add a comment |
add a comment |
This converts the value to a string, takes the left 14 chars and add the 00:00:00 values
select left(convert(varchar , datecol, 121),14) + '00:00:00' as mydatecol from table
answered Nov 26 '18 at 11:36
WilhelmWilhelm
997
add a comment |
This converts the value to a string, takes the left 14 chars and add the 00:00:00 values
select left(convert(varchar , datecol, 121),14) + '00:00:00' as mydatecol from table
answered Nov 26 '18 at 11:36
WilhelmWilhelm
997
add a comment |
This converts the value to a string, takes the left 14 chars and add the 00:00:00 values
select left(convert(varchar , datecol, 121),14) + '00:00:00' as mydatecol from table
answered Nov 26 '18 at 11:36
WilhelmWilhelm
997
This converts the value to a string, takes the left 14 chars and add the 00:00:00 values
select left(convert(varchar , datecol, 121),14) + '00:00:00' as mydatecol from table
answered Nov 26 '18 at 11:36
WilhelmWilhelm
997
answered Nov 26 '18 at 11:36
WilhelmWilhelm
997
answered Nov 26 '18 at 11:36
WilhelmWilhelm
997
answered Nov 26 '18 at 11:36
WilhelmWilhelm
997
997
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53479857%2fround-datetime-to-the-beginning-of-the-current-hour%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
