SQL date insertion from Excel results in wrong date value
0
I have problem executing part of my SQL query from Excel. Note that the same query works from my Manager's PC, and both are on the same Regional settings in CP.
Here's code:
Do While Range("A" & CStr(Red)).Value <> ""
Sql_Query = "UPDATE [DB-NAME] SET [Expiration Date] = '" & Format(Trim(Range("H" & CStr(Red)).Value), "yyyy-mm-dd") & _
"' WHERE (No_ = '" & Trim(Range("A" & CStr(Red)).Value) & "') AND ([Pallet Place No_] = '" & Trim(Range("B" & CStr(Red)).Value) & "') AND " & _
"([Item No_] = '" & Trim(Range("C" & CStr(Red)).Value) & "')"
Set Rs = Conn.Execute(Sql_Query)
'Rs.Close ' Closing query
Set Rs = Nothing
Red = Red + 1 'Switching to the next row after one is finished
Loop
When I execute this part, Mr. Code inserts code like this:
"UPDATE [DB-NAME] SET [Expiration Date] = '7627-09-30' WHERE (No_ = '267917') AND ([Pallet Place No_] = '11110102') AND ([Item No_] = '116000')"
Note: this is text that 'Add Watch' option returns.
Even though in that 'H' column date is like '2018-12-30'.
Cell format in Excel doesn't make change. Its not important if its General or Text or Date or Custom-date, problem remains. And this thing with Mr. Code is consistent.
sql excel vba excel-2010 sql-date-functions
edited Nov 20 '18 at 8:49
halfer
14.5k758111
asked Nov 19 '18 at 9:30
abblleabblle
13
add a comment |
0
I have problem executing part of my SQL query from Excel. Note that the same query works from my Manager's PC, and both are on the same Regional settings in CP.
Here's code:
Do While Range("A" & CStr(Red)).Value <> ""
Sql_Query = "UPDATE [DB-NAME] SET [Expiration Date] = '" & Format(Trim(Range("H" & CStr(Red)).Value), "yyyy-mm-dd") & _
"' WHERE (No_ = '" & Trim(Range("A" & CStr(Red)).Value) & "') AND ([Pallet Place No_] = '" & Trim(Range("B" & CStr(Red)).Value) & "') AND " & _
"([Item No_] = '" & Trim(Range("C" & CStr(Red)).Value) & "')"
Set Rs = Conn.Execute(Sql_Query)
'Rs.Close ' Closing query
Set Rs = Nothing
Red = Red + 1 'Switching to the next row after one is finished
Loop
When I execute this part, Mr. Code inserts code like this:
"UPDATE [DB-NAME] SET [Expiration Date] = '7627-09-30' WHERE (No_ = '267917') AND ([Pallet Place No_] = '11110102') AND ([Item No_] = '116000')"
Note: this is text that 'Add Watch' option returns.
Even though in that 'H' column date is like '2018-12-30'.
Cell format in Excel doesn't make change. Its not important if its General or Text or Date or Custom-date, problem remains. And this thing with Mr. Code is consistent.
sql excel vba excel-2010 sql-date-functions
edited Nov 20 '18 at 8:49
halfer
14.5k758111
asked Nov 19 '18 at 9:30
abblleabblle
13
In some dialects of SQL you need to surround date literals with different characters (usually # ) rather than single quotes. Could this be it?
– Harassed Dad
Nov 19 '18 at 9:46
@HarassedDad I dont know if thats possible problem because same code works on another PC with the same SQL AND Regional settings.
– abblle
Nov 19 '18 at 9:55
Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions.
– halfer
Nov 20 '18 at 8:49
add a comment |
0
0
0
I have problem executing part of my SQL query from Excel. Note that the same query works from my Manager's PC, and both are on the same Regional settings in CP.
Here's code:
Do While Range("A" & CStr(Red)).Value <> ""
Sql_Query = "UPDATE [DB-NAME] SET [Expiration Date] = '" & Format(Trim(Range("H" & CStr(Red)).Value), "yyyy-mm-dd") & _
"' WHERE (No_ = '" & Trim(Range("A" & CStr(Red)).Value) & "') AND ([Pallet Place No_] = '" & Trim(Range("B" & CStr(Red)).Value) & "') AND " & _
"([Item No_] = '" & Trim(Range("C" & CStr(Red)).Value) & "')"
Set Rs = Conn.Execute(Sql_Query)
'Rs.Close ' Closing query
Set Rs = Nothing
Red = Red + 1 'Switching to the next row after one is finished
Loop
When I execute this part, Mr. Code inserts code like this:
"UPDATE [DB-NAME] SET [Expiration Date] = '7627-09-30' WHERE (No_ = '267917') AND ([Pallet Place No_] = '11110102') AND ([Item No_] = '116000')"
Note: this is text that 'Add Watch' option returns.
Even though in that 'H' column date is like '2018-12-30'.
Cell format in Excel doesn't make change. Its not important if its General or Text or Date or Custom-date, problem remains. And this thing with Mr. Code is consistent.
sql excel vba excel-2010 sql-date-functions
edited Nov 20 '18 at 8:49
halfer
14.5k758111
asked Nov 19 '18 at 9:30
abblleabblle
13
I have problem executing part of my SQL query from Excel. Note that the same query works from my Manager's PC, and both are on the same Regional settings in CP.
Here's code:
Do While Range("A" & CStr(Red)).Value <> ""
Sql_Query = "UPDATE [DB-NAME] SET [Expiration Date] = '" & Format(Trim(Range("H" & CStr(Red)).Value), "yyyy-mm-dd") & _
"' WHERE (No_ = '" & Trim(Range("A" & CStr(Red)).Value) & "') AND ([Pallet Place No_] = '" & Trim(Range("B" & CStr(Red)).Value) & "') AND " & _
"([Item No_] = '" & Trim(Range("C" & CStr(Red)).Value) & "')"
Set Rs = Conn.Execute(Sql_Query)
'Rs.Close ' Closing query
Set Rs = Nothing
Red = Red + 1 'Switching to the next row after one is finished
Loop
When I execute this part, Mr. Code inserts code like this:
"UPDATE [DB-NAME] SET [Expiration Date] = '7627-09-30' WHERE (No_ = '267917') AND ([Pallet Place No_] = '11110102') AND ([Item No_] = '116000')"
Note: this is text that 'Add Watch' option returns.
Even though in that 'H' column date is like '2018-12-30'.
Cell format in Excel doesn't make change. Its not important if its General or Text or Date or Custom-date, problem remains. And this thing with Mr. Code is consistent.
sql excel vba excel-2010 sql-date-functions
sql excel vba excel-2010 sql-date-functions
edited Nov 20 '18 at 8:49
halfer
14.5k758111
asked Nov 19 '18 at 9:30
abblleabblle
13
edited Nov 20 '18 at 8:49
halfer
14.5k758111
asked Nov 19 '18 at 9:30
abblleabblle
13
edited Nov 20 '18 at 8:49
halfer
14.5k758111
edited Nov 20 '18 at 8:49
halfer
14.5k758111
edited Nov 20 '18 at 8:49
halfer
14.5k758111
14.5k758111
asked Nov 19 '18 at 9:30
abblleabblle
13
asked Nov 19 '18 at 9:30
abblleabblle
13
asked Nov 19 '18 at 9:30
abblleabblle
13
13
In some dialects of SQL you need to surround date literals with different characters (usually # ) rather than single quotes. Could this be it?
– Harassed Dad
Nov 19 '18 at 9:46
@HarassedDad I dont know if thats possible problem because same code works on another PC with the same SQL AND Regional settings.
– abblle
Nov 19 '18 at 9:55
Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions.
– halfer
Nov 20 '18 at 8:49
add a comment |
In some dialects of SQL you need to surround date literals with different characters (usually # ) rather than single quotes. Could this be it?
– Harassed Dad
Nov 19 '18 at 9:46
@HarassedDad I dont know if thats possible problem because same code works on another PC with the same SQL AND Regional settings.
– abblle
Nov 19 '18 at 9:55
Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions.
– halfer
Nov 20 '18 at 8:49
In some dialects of SQL you need to surround date literals with different characters (usually # ) rather than single quotes. Could this be it?
– Harassed Dad
Nov 19 '18 at 9:46
In some dialects of SQL you need to surround date literals with different characters (usually # ) rather than single quotes. Could this be it?
– Harassed Dad
Nov 19 '18 at 9:46
@HarassedDad I dont know if thats possible problem because same code works on another PC with the same SQL AND Regional settings.
– abblle
Nov 19 '18 at 9:55
@HarassedDad I dont know if thats possible problem because same code works on another PC with the same SQL AND Regional settings.
– abblle
Nov 19 '18 at 9:55
Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions.
– halfer
Nov 20 '18 at 8:49
Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions.
– halfer
Nov 20 '18 at 8:49
add a comment |
1 Answer
1
active
oldest
votes
0
Is that query works at all from yours computer? Try update other field (it's better number type).
answered Nov 20 '18 at 8:48
Almantas BendoraitisAlmantas Bendoraitis
112
other parts works fine, but whenever I try to insert date from any document to a db, I have same problem as here.
– abblle
Nov 20 '18 at 9:24
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%2f53371699%2fsql-date-insertion-from-excel-results-in-wrong-date-value%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
0
Is that query works at all from yours computer? Try update other field (it's better number type).
answered Nov 20 '18 at 8:48
Almantas BendoraitisAlmantas Bendoraitis
112
other parts works fine, but whenever I try to insert date from any document to a db, I have same problem as here.
– abblle
Nov 20 '18 at 9:24
add a comment |
0
Is that query works at all from yours computer? Try update other field (it's better number type).
answered Nov 20 '18 at 8:48
Almantas BendoraitisAlmantas Bendoraitis
112
other parts works fine, but whenever I try to insert date from any document to a db, I have same problem as here.
– abblle
Nov 20 '18 at 9:24
add a comment |
0
0
0
Is that query works at all from yours computer? Try update other field (it's better number type).
answered Nov 20 '18 at 8:48
Almantas BendoraitisAlmantas Bendoraitis
112
Is that query works at all from yours computer? Try update other field (it's better number type).
answered Nov 20 '18 at 8:48
Almantas BendoraitisAlmantas Bendoraitis
112
answered Nov 20 '18 at 8:48
Almantas BendoraitisAlmantas Bendoraitis
112
answered Nov 20 '18 at 8:48
Almantas BendoraitisAlmantas Bendoraitis
112
answered Nov 20 '18 at 8:48
Almantas BendoraitisAlmantas Bendoraitis
112
112
other parts works fine, but whenever I try to insert date from any document to a db, I have same problem as here.
– abblle
Nov 20 '18 at 9:24
add a comment |
other parts works fine, but whenever I try to insert date from any document to a db, I have same problem as here.
– abblle
Nov 20 '18 at 9:24
other parts works fine, but whenever I try to insert date from any document to a db, I have same problem as here.
– abblle
Nov 20 '18 at 9:24
other parts works fine, but whenever I try to insert date from any document to a db, I have same problem as here.
– abblle
Nov 20 '18 at 9:24
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%2f53371699%2fsql-date-insertion-from-excel-results-in-wrong-date-value%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
In some dialects of SQL you need to surround date literals with different characters (usually # ) rather than single quotes. Could this be it?
– Harassed Dad
Nov 19 '18 at 9:46
@HarassedDad I dont know if thats possible problem because same code works on another PC with the same SQL AND Regional settings.
– abblle
Nov 19 '18 at 9:55
Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions.
– halfer
Nov 20 '18 at 8:49