How to get the start and end of a day in T-SQL when daylight savings is enabled?
I am currently working on a SQL Server script where we collect aggregated data on a daily basis for last 'n' days. In order to do this, we create a temp table with the start time and end time for all days for the last 'n' days. The code is as below.
DECLARE @Tbl TABLE (Begin DATETIME, End DATETIME)
DECLARE @i INT = 1
DECLARE @begin DATETIME = DATEADD(DD, DATEDIFF(DD, 1, GETDATE()), 0)
DECLARE @end DATETIME = DATEADD(DD, 1, @begin)
WHILE @i <= @n
INSERT INTO @Tbl
SELECT @begin, @end
SET @end = @begin
SET @begin = DATEADD(DAY, -1, @end)
SET @i = @i + 1
END
We convert these dates to UTC by calling an inbuilt function which does the operation correctly as expected but throws an error for November 4th 2018 00:00:00 as this is not a valid local time.
The issue with the above code is that it generates the date with the 0th hour of begin date and 0th hour of end date. Although some of these dates might be impractical due to daylight savings.
For example, if you consider the Brazil timezone, daylight saving was enabled on November 4th at 00:00:00 to 01:00:00 which makes the date 2018-11-04 00:00:00 incorrect to be converted to UTC.
Is there any known functions which can be used to pass a date and get the valid start time for the day and valid end time for the day in local time?
sql-server tsql dst edited Nov 23 '18 at 3:21
aduguid
2,13661031
asked Nov 23 '18 at 2:15
CodeIngot
615
add a comment |
I am currently working on a SQL Server script where we collect aggregated data on a daily basis for last 'n' days. In order to do this, we create a temp table with the start time and end time for all days for the last 'n' days. The code is as below.
DECLARE @Tbl TABLE (Begin DATETIME, End DATETIME)
DECLARE @i INT = 1
DECLARE @begin DATETIME = DATEADD(DD, DATEDIFF(DD, 1, GETDATE()), 0)
DECLARE @end DATETIME = DATEADD(DD, 1, @begin)
WHILE @i <= @n
INSERT INTO @Tbl
SELECT @begin, @end
SET @end = @begin
SET @begin = DATEADD(DAY, -1, @end)
SET @i = @i + 1
END
We convert these dates to UTC by calling an inbuilt function which does the operation correctly as expected but throws an error for November 4th 2018 00:00:00 as this is not a valid local time.
The issue with the above code is that it generates the date with the 0th hour of begin date and 0th hour of end date. Although some of these dates might be impractical due to daylight savings.
For example, if you consider the Brazil timezone, daylight saving was enabled on November 4th at 00:00:00 to 01:00:00 which makes the date 2018-11-04 00:00:00 incorrect to be converted to UTC.
Is there any known functions which can be used to pass a date and get the valid start time for the day and valid end time for the day in local time?
sql-server tsql dst edited Nov 23 '18 at 3:21
aduguid
2,13661031
asked Nov 23 '18 at 2:15
CodeIngot
615
1
What version of SQL Server are you using?
– aduguid
Nov 23 '18 at 3:21
I believe you're coming at the problem backwards. You should start with UTC and convert to/from local time at the display layer. This will likely present you with different difficulties, but they should be easier to deal with. Not posting an answer, as I don't really have one.
– user5151179
Nov 23 '18 at 3:32
Since we need to get the customer data from their timezone, we start with local time instead of starting with UTC. If I start with UTC, removal of 24 hours for everyday would lead to time being set to 23:00:00 for 3rd November and 2nd November. Instead the difference calculated must be 23 hours for 4th November automatically instead of 24 hours.
– CodeIngot
Nov 23 '18 at 4:34
We are using Microsoft SQL Server 2012.
– CodeIngot
Nov 23 '18 at 4:36
I think you're doing it wrong. November 4th 2018 00:00:00 is invalid for you but perfectly valid for someone who is not observing daylight savings time. I would ask for complete date time including the offset (+xx:xx) part and use it to convert the datetime to UTC.
– Salman A
Nov 23 '18 at 10:51
add a comment |
I am currently working on a SQL Server script where we collect aggregated data on a daily basis for last 'n' days. In order to do this, we create a temp table with the start time and end time for all days for the last 'n' days. The code is as below.
DECLARE @Tbl TABLE (Begin DATETIME, End DATETIME)
DECLARE @i INT = 1
DECLARE @begin DATETIME = DATEADD(DD, DATEDIFF(DD, 1, GETDATE()), 0)
DECLARE @end DATETIME = DATEADD(DD, 1, @begin)
WHILE @i <= @n
INSERT INTO @Tbl
SELECT @begin, @end
SET @end = @begin
SET @begin = DATEADD(DAY, -1, @end)
SET @i = @i + 1
END
We convert these dates to UTC by calling an inbuilt function which does the operation correctly as expected but throws an error for November 4th 2018 00:00:00 as this is not a valid local time.
The issue with the above code is that it generates the date with the 0th hour of begin date and 0th hour of end date. Although some of these dates might be impractical due to daylight savings.
For example, if you consider the Brazil timezone, daylight saving was enabled on November 4th at 00:00:00 to 01:00:00 which makes the date 2018-11-04 00:00:00 incorrect to be converted to UTC.
Is there any known functions which can be used to pass a date and get the valid start time for the day and valid end time for the day in local time?
sql-server tsql dst edited Nov 23 '18 at 3:21
aduguid
2,13661031
asked Nov 23 '18 at 2:15
CodeIngot
615
I am currently working on a SQL Server script where we collect aggregated data on a daily basis for last 'n' days. In order to do this, we create a temp table with the start time and end time for all days for the last 'n' days. The code is as below.
DECLARE @Tbl TABLE (Begin DATETIME, End DATETIME)
DECLARE @i INT = 1
DECLARE @begin DATETIME = DATEADD(DD, DATEDIFF(DD, 1, GETDATE()), 0)
DECLARE @end DATETIME = DATEADD(DD, 1, @begin)
WHILE @i <= @n
INSERT INTO @Tbl
SELECT @begin, @end
SET @end = @begin
SET @begin = DATEADD(DAY, -1, @end)
SET @i = @i + 1
END
We convert these dates to UTC by calling an inbuilt function which does the operation correctly as expected but throws an error for November 4th 2018 00:00:00 as this is not a valid local time.
The issue with the above code is that it generates the date with the 0th hour of begin date and 0th hour of end date. Although some of these dates might be impractical due to daylight savings.
For example, if you consider the Brazil timezone, daylight saving was enabled on November 4th at 00:00:00 to 01:00:00 which makes the date 2018-11-04 00:00:00 incorrect to be converted to UTC.
Is there any known functions which can be used to pass a date and get the valid start time for the day and valid end time for the day in local time?
sql-server tsql dst sql-server tsql dst edited Nov 23 '18 at 3:21
aduguid
2,13661031
asked Nov 23 '18 at 2:15
CodeIngot
615
edited Nov 23 '18 at 3:21
aduguid
2,13661031
asked Nov 23 '18 at 2:15
CodeIngot
615
edited Nov 23 '18 at 3:21
aduguid
2,13661031
edited Nov 23 '18 at 3:21
aduguid
2,13661031
edited Nov 23 '18 at 3:21
aduguid
2,13661031
2,13661031
asked Nov 23 '18 at 2:15
CodeIngot
615
asked Nov 23 '18 at 2:15
CodeIngot
615
asked Nov 23 '18 at 2:15
CodeIngot
615
615
1
What version of SQL Server are you using?
– aduguid
Nov 23 '18 at 3:21
I believe you're coming at the problem backwards. You should start with UTC and convert to/from local time at the display layer. This will likely present you with different difficulties, but they should be easier to deal with. Not posting an answer, as I don't really have one.
– user5151179
Nov 23 '18 at 3:32
Since we need to get the customer data from their timezone, we start with local time instead of starting with UTC. If I start with UTC, removal of 24 hours for everyday would lead to time being set to 23:00:00 for 3rd November and 2nd November. Instead the difference calculated must be 23 hours for 4th November automatically instead of 24 hours.
– CodeIngot
Nov 23 '18 at 4:34
We are using Microsoft SQL Server 2012.
– CodeIngot
Nov 23 '18 at 4:36
I think you're doing it wrong. November 4th 2018 00:00:00 is invalid for you but perfectly valid for someone who is not observing daylight savings time. I would ask for complete date time including the offset (+xx:xx) part and use it to convert the datetime to UTC.
– Salman A
Nov 23 '18 at 10:51
add a comment |
1
What version of SQL Server are you using?
– aduguid
Nov 23 '18 at 3:21
I believe you're coming at the problem backwards. You should start with UTC and convert to/from local time at the display layer. This will likely present you with different difficulties, but they should be easier to deal with. Not posting an answer, as I don't really have one.
– user5151179
Nov 23 '18 at 3:32
Since we need to get the customer data from their timezone, we start with local time instead of starting with UTC. If I start with UTC, removal of 24 hours for everyday would lead to time being set to 23:00:00 for 3rd November and 2nd November. Instead the difference calculated must be 23 hours for 4th November automatically instead of 24 hours.
– CodeIngot
Nov 23 '18 at 4:34
We are using Microsoft SQL Server 2012.
– CodeIngot
Nov 23 '18 at 4:36
I think you're doing it wrong. November 4th 2018 00:00:00 is invalid for you but perfectly valid for someone who is not observing daylight savings time. I would ask for complete date time including the offset (+xx:xx) part and use it to convert the datetime to UTC.
– Salman A
Nov 23 '18 at 10:51
1
1
What version of SQL Server are you using?
– aduguid
Nov 23 '18 at 3:21
What version of SQL Server are you using?
– aduguid
Nov 23 '18 at 3:21
I believe you're coming at the problem backwards. You should start with UTC and convert to/from local time at the display layer. This will likely present you with different difficulties, but they should be easier to deal with. Not posting an answer, as I don't really have one.
– user5151179
Nov 23 '18 at 3:32
I believe you're coming at the problem backwards. You should start with UTC and convert to/from local time at the display layer. This will likely present you with different difficulties, but they should be easier to deal with. Not posting an answer, as I don't really have one.
– user5151179
Nov 23 '18 at 3:32
Since we need to get the customer data from their timezone, we start with local time instead of starting with UTC. If I start with UTC, removal of 24 hours for everyday would lead to time being set to 23:00:00 for 3rd November and 2nd November. Instead the difference calculated must be 23 hours for 4th November automatically instead of 24 hours.
– CodeIngot
Nov 23 '18 at 4:34
Since we need to get the customer data from their timezone, we start with local time instead of starting with UTC. If I start with UTC, removal of 24 hours for everyday would lead to time being set to 23:00:00 for 3rd November and 2nd November. Instead the difference calculated must be 23 hours for 4th November automatically instead of 24 hours.
– CodeIngot
Nov 23 '18 at 4:34
We are using Microsoft SQL Server 2012.
– CodeIngot
Nov 23 '18 at 4:36
We are using Microsoft SQL Server 2012.
– CodeIngot
Nov 23 '18 at 4:36
I think you're doing it wrong. November 4th 2018 00:00:00 is invalid for you but perfectly valid for someone who is not observing daylight savings time. I would ask for complete date time including the offset (
+xx:xx) part and use it to convert the datetime to UTC.– Salman A
Nov 23 '18 at 10:51
I think you're doing it wrong. November 4th 2018 00:00:00 is invalid for you but perfectly valid for someone who is not observing daylight savings time. I would ask for complete date time including the offset (
+xx:xx) part and use it to convert the datetime to UTC.– Salman A
Nov 23 '18 at 10:51
add a comment |
1 Answer
1
active
oldest
votes
GETUTCDATE()
Returns the UTC Date and Time
answered Nov 23 '18 at 8:45
CAGDAS AYDIN
5018
You can user use GETUTCDATE() like GETGETDATE()
– CAGDAS AYDIN
Nov 23 '18 at 8:46
add a comment |
Your Answer
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%2f53439900%2fhow-to-get-the-start-and-end-of-a-day-in-t-sql-when-daylight-savings-is-enabled%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
GETUTCDATE()
Returns the UTC Date and Time
answered Nov 23 '18 at 8:45
CAGDAS AYDIN
5018
You can user use GETUTCDATE() like GETGETDATE()
– CAGDAS AYDIN
Nov 23 '18 at 8:46
add a comment |
GETUTCDATE()
Returns the UTC Date and Time
answered Nov 23 '18 at 8:45
CAGDAS AYDIN
5018
You can user use GETUTCDATE() like GETGETDATE()
– CAGDAS AYDIN
Nov 23 '18 at 8:46
add a comment |
GETUTCDATE()
Returns the UTC Date and Time
answered Nov 23 '18 at 8:45
CAGDAS AYDIN
5018
GETUTCDATE()
Returns the UTC Date and Time
answered Nov 23 '18 at 8:45
CAGDAS AYDIN
5018
answered Nov 23 '18 at 8:45
CAGDAS AYDIN
5018
answered Nov 23 '18 at 8:45
CAGDAS AYDIN
5018
answered Nov 23 '18 at 8:45
CAGDAS AYDIN
5018
5018
You can user use GETUTCDATE() like GETGETDATE()
– CAGDAS AYDIN
Nov 23 '18 at 8:46
add a comment |
You can user use GETUTCDATE() like GETGETDATE()
– CAGDAS AYDIN
Nov 23 '18 at 8:46
You can user use GETUTCDATE() like GETGETDATE()
– CAGDAS AYDIN
Nov 23 '18 at 8:46
You can user use GETUTCDATE() like GETGETDATE()
– CAGDAS AYDIN
Nov 23 '18 at 8:46
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53439900%2fhow-to-get-the-start-and-end-of-a-day-in-t-sql-when-daylight-savings-is-enabled%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
1
What version of SQL Server are you using?
– aduguid
Nov 23 '18 at 3:21
I believe you're coming at the problem backwards. You should start with UTC and convert to/from local time at the display layer. This will likely present you with different difficulties, but they should be easier to deal with. Not posting an answer, as I don't really have one.
– user5151179
Nov 23 '18 at 3:32
Since we need to get the customer data from their timezone, we start with local time instead of starting with UTC. If I start with UTC, removal of 24 hours for everyday would lead to time being set to 23:00:00 for 3rd November and 2nd November. Instead the difference calculated must be 23 hours for 4th November automatically instead of 24 hours.
– CodeIngot
Nov 23 '18 at 4:34
We are using Microsoft SQL Server 2012.
– CodeIngot
Nov 23 '18 at 4:36
I think you're doing it wrong. November 4th 2018 00:00:00 is invalid for you but perfectly valid for someone who is not observing daylight savings time. I would ask for complete date time including the offset (
+xx:xx) part and use it to convert the datetime to UTC.– Salman A
Nov 23 '18 at 10:51