SQL server Indian Timezone Date
1
I'm using SQL server 2008 R2, (Located in US location)
I have a SQL Job to send email notifications to Customers. Based on their Birthday I'm getting the data from Database as below:
SELECT CustomerId,
CustomerName,
Email,
Dob,
FROM Customers
WHERE DAY(Dob) = DAY(GETDATE())
AND MONTH(Dob) = MONTH(GETDATE())
And the JOB is scheduled at 9:30 AM IST.
Problem here is, since the Server is located in US and Job is running at morning 9:30 AM IST, It sends same email twice a day.
How can I handle such situation? It there any way to handle TimeZone? Please help me in this.
sql sql-server-2008-r2 timezone-offset sql-job
asked Nov 24 '18 at 6:14
55SK5555SK55
609
add a comment |
1
I'm using SQL server 2008 R2, (Located in US location)
I have a SQL Job to send email notifications to Customers. Based on their Birthday I'm getting the data from Database as below:
SELECT CustomerId,
CustomerName,
Email,
Dob,
FROM Customers
WHERE DAY(Dob) = DAY(GETDATE())
AND MONTH(Dob) = MONTH(GETDATE())
And the JOB is scheduled at 9:30 AM IST.
Problem here is, since the Server is located in US and Job is running at morning 9:30 AM IST, It sends same email twice a day.
How can I handle such situation? It there any way to handle TimeZone? Please help me in this.
sql sql-server-2008-r2 timezone-offset sql-job
asked Nov 24 '18 at 6:14
55SK5555SK55
609
add a comment |
1
1
1
I'm using SQL server 2008 R2, (Located in US location)
I have a SQL Job to send email notifications to Customers. Based on their Birthday I'm getting the data from Database as below:
SELECT CustomerId,
CustomerName,
Email,
Dob,
FROM Customers
WHERE DAY(Dob) = DAY(GETDATE())
AND MONTH(Dob) = MONTH(GETDATE())
And the JOB is scheduled at 9:30 AM IST.
Problem here is, since the Server is located in US and Job is running at morning 9:30 AM IST, It sends same email twice a day.
How can I handle such situation? It there any way to handle TimeZone? Please help me in this.
sql sql-server-2008-r2 timezone-offset sql-job
asked Nov 24 '18 at 6:14
55SK5555SK55
609
I'm using SQL server 2008 R2, (Located in US location)
I have a SQL Job to send email notifications to Customers. Based on their Birthday I'm getting the data from Database as below:
SELECT CustomerId,
CustomerName,
Email,
Dob,
FROM Customers
WHERE DAY(Dob) = DAY(GETDATE())
AND MONTH(Dob) = MONTH(GETDATE())
And the JOB is scheduled at 9:30 AM IST.
Problem here is, since the Server is located in US and Job is running at morning 9:30 AM IST, It sends same email twice a day.
How can I handle such situation? It there any way to handle TimeZone? Please help me in this.
sql sql-server-2008-r2 timezone-offset sql-job
sql sql-server-2008-r2 timezone-offset sql-job
asked Nov 24 '18 at 6:14
55SK5555SK55
609
asked Nov 24 '18 at 6:14
55SK5555SK55
609
edited Nov 24 '18 at 6:40
55SK55
asked Nov 24 '18 at 6:14
55SK5555SK55
609
asked Nov 24 '18 at 6:14
55SK5555SK55
609
asked Nov 24 '18 at 6:14
55SK5555SK55
609
609
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
You can try below
SELECT CustomerId,
CustomerName,
Email,
Dob,
FROM Customers
WHERE DAY(Dob) = DAY(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
AND MONTH(Dob) = MONTH(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
answered Nov 24 '18 at 6:35
fa06fa06
13.7k2917
AT TIME ZONEis feature of SQL Server 2016. I am using SQL server 2008 R2
– 55SK55
Nov 24 '18 at 6:38
@55SK55, modify your question
– fa06
Nov 24 '18 at 6:39
heredtDateisGETDATE()right?
– 55SK55
Nov 24 '18 at 6:45
@55SK55, yes it's getdate()
– fa06
Nov 24 '18 at 6:46
This assumes that -6 is always the correct offset. It won't be if the server is in a time zone that uses daylight saving time, which is likely. You should instead get the UTC date (with eitherSYSUTCDATETIMEorGETUTCDATE), and add +5:30 to that.
– Matt Johnson
Nov 29 '18 at 21:05
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
});
}
});
draft saved
draft discarded
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%2f53455671%2fsql-server-indian-timezone-date%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
1
You can try below
SELECT CustomerId,
CustomerName,
Email,
Dob,
FROM Customers
WHERE DAY(Dob) = DAY(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
AND MONTH(Dob) = MONTH(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
answered Nov 24 '18 at 6:35
fa06fa06
13.7k2917
AT TIME ZONEis feature of SQL Server 2016. I am using SQL server 2008 R2
– 55SK55
Nov 24 '18 at 6:38
@55SK55, modify your question
– fa06
Nov 24 '18 at 6:39
heredtDateisGETDATE()right?
– 55SK55
Nov 24 '18 at 6:45
@55SK55, yes it's getdate()
– fa06
Nov 24 '18 at 6:46
This assumes that -6 is always the correct offset. It won't be if the server is in a time zone that uses daylight saving time, which is likely. You should instead get the UTC date (with eitherSYSUTCDATETIMEorGETUTCDATE), and add +5:30 to that.
– Matt Johnson
Nov 29 '18 at 21:05
add a comment |
1
You can try below
SELECT CustomerId,
CustomerName,
Email,
Dob,
FROM Customers
WHERE DAY(Dob) = DAY(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
AND MONTH(Dob) = MONTH(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
answered Nov 24 '18 at 6:35
fa06fa06
13.7k2917
AT TIME ZONEis feature of SQL Server 2016. I am using SQL server 2008 R2
– 55SK55
Nov 24 '18 at 6:38
@55SK55, modify your question
– fa06
Nov 24 '18 at 6:39
heredtDateisGETDATE()right?
– 55SK55
Nov 24 '18 at 6:45
@55SK55, yes it's getdate()
– fa06
Nov 24 '18 at 6:46
This assumes that -6 is always the correct offset. It won't be if the server is in a time zone that uses daylight saving time, which is likely. You should instead get the UTC date (with eitherSYSUTCDATETIMEorGETUTCDATE), and add +5:30 to that.
– Matt Johnson
Nov 29 '18 at 21:05
add a comment |
1
1
1
You can try below
SELECT CustomerId,
CustomerName,
Email,
Dob,
FROM Customers
WHERE DAY(Dob) = DAY(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
AND MONTH(Dob) = MONTH(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
answered Nov 24 '18 at 6:35
fa06fa06
13.7k2917
You can try below
SELECT CustomerId,
CustomerName,
Email,
Dob,
FROM Customers
WHERE DAY(Dob) = DAY(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
AND MONTH(Dob) = MONTH(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
answered Nov 24 '18 at 6:35
fa06fa06
13.7k2917
edited Nov 24 '18 at 6:48
answered Nov 24 '18 at 6:35
fa06fa06
13.7k2917
answered Nov 24 '18 at 6:35
fa06fa06
13.7k2917
answered Nov 24 '18 at 6:35
fa06fa06
13.7k2917
13.7k2917
AT TIME ZONEis feature of SQL Server 2016. I am using SQL server 2008 R2
– 55SK55
Nov 24 '18 at 6:38
@55SK55, modify your question
– fa06
Nov 24 '18 at 6:39
heredtDateisGETDATE()right?
– 55SK55
Nov 24 '18 at 6:45
@55SK55, yes it's getdate()
– fa06
Nov 24 '18 at 6:46
This assumes that -6 is always the correct offset. It won't be if the server is in a time zone that uses daylight saving time, which is likely. You should instead get the UTC date (with eitherSYSUTCDATETIMEorGETUTCDATE), and add +5:30 to that.
– Matt Johnson
Nov 29 '18 at 21:05
add a comment |
AT TIME ZONEis feature of SQL Server 2016. I am using SQL server 2008 R2
– 55SK55
Nov 24 '18 at 6:38
@55SK55, modify your question
– fa06
Nov 24 '18 at 6:39
heredtDateisGETDATE()right?
– 55SK55
Nov 24 '18 at 6:45
@55SK55, yes it's getdate()
– fa06
Nov 24 '18 at 6:46
This assumes that -6 is always the correct offset. It won't be if the server is in a time zone that uses daylight saving time, which is likely. You should instead get the UTC date (with eitherSYSUTCDATETIMEorGETUTCDATE), and add +5:30 to that.
– Matt Johnson
Nov 29 '18 at 21:05
AT TIME ZONE is feature of SQL Server 2016. I am using SQL server 2008 R2– 55SK55
Nov 24 '18 at 6:38
AT TIME ZONE is feature of SQL Server 2016. I am using SQL server 2008 R2– 55SK55
Nov 24 '18 at 6:38
@55SK55, modify your question
– fa06
Nov 24 '18 at 6:39
@55SK55, modify your question
– fa06
Nov 24 '18 at 6:39
here
dtDate is GETDATE() right?– 55SK55
Nov 24 '18 at 6:45
here
dtDate is GETDATE() right?– 55SK55
Nov 24 '18 at 6:45
@55SK55, yes it's getdate()
– fa06
Nov 24 '18 at 6:46
@55SK55, yes it's getdate()
– fa06
Nov 24 '18 at 6:46
This assumes that -6 is always the correct offset. It won't be if the server is in a time zone that uses daylight saving time, which is likely. You should instead get the UTC date (with either
SYSUTCDATETIME or GETUTCDATE), and add +5:30 to that.– Matt Johnson
Nov 29 '18 at 21:05
This assumes that -6 is always the correct offset. It won't be if the server is in a time zone that uses daylight saving time, which is likely. You should instead get the UTC date (with either
SYSUTCDATETIME or GETUTCDATE), and add +5:30 to that.– Matt Johnson
Nov 29 '18 at 21:05
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53455671%2fsql-server-indian-timezone-date%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
