Why is DATEDIFF parsing these dates as dd/mm/yyyy?
I do:
SELECT DATEDIFF(month, Convert(datetime,'01/10/2018',103),
Convert(datetime,'30/04/2019',103)) AS 'Month1',
DATEDIFF(month, '10/01/2018','04/30/2019') AS 'Months2',
DATEDIFF(month, '10/02/2018','05/01/2019') AS 'Months3'
Please show me why it return 6,6,7?
sql datediff
edited Nov 14 '18 at 3:03
gkrogers
6,90822533
asked Nov 14 '18 at 2:32
user10649461user10649461
6
add a comment |
I do:
SELECT DATEDIFF(month, Convert(datetime,'01/10/2018',103),
Convert(datetime,'30/04/2019',103)) AS 'Month1',
DATEDIFF(month, '10/01/2018','04/30/2019') AS 'Months2',
DATEDIFF(month, '10/02/2018','05/01/2019') AS 'Months3'
Please show me why it return 6,6,7?
sql datediff
edited Nov 14 '18 at 3:03
gkrogers
6,90822533
asked Nov 14 '18 at 2:32
user10649461user10649461
6
1
What's your dbms? I think you are using sqlserver You need to take a look docs.microsoft.com/zh-tw/sql/t-sql/functions/…
– D-Shih
Nov 14 '18 at 2:34
Just use standard formats for dates -- YYYY-MM-DD.
– Gordon Linoff
Nov 14 '18 at 3:50
add a comment |
I do:
SELECT DATEDIFF(month, Convert(datetime,'01/10/2018',103),
Convert(datetime,'30/04/2019',103)) AS 'Month1',
DATEDIFF(month, '10/01/2018','04/30/2019') AS 'Months2',
DATEDIFF(month, '10/02/2018','05/01/2019') AS 'Months3'
Please show me why it return 6,6,7?
sql datediff
edited Nov 14 '18 at 3:03
gkrogers
6,90822533
asked Nov 14 '18 at 2:32
user10649461user10649461
6
I do:
SELECT DATEDIFF(month, Convert(datetime,'01/10/2018',103),
Convert(datetime,'30/04/2019',103)) AS 'Month1',
DATEDIFF(month, '10/01/2018','04/30/2019') AS 'Months2',
DATEDIFF(month, '10/02/2018','05/01/2019') AS 'Months3'
Please show me why it return 6,6,7?
sql datediff
sql datediff
edited Nov 14 '18 at 3:03
gkrogers
6,90822533
asked Nov 14 '18 at 2:32
user10649461user10649461
6
edited Nov 14 '18 at 3:03
gkrogers
6,90822533
asked Nov 14 '18 at 2:32
user10649461user10649461
6
edited Nov 14 '18 at 3:03
gkrogers
6,90822533
edited Nov 14 '18 at 3:03
gkrogers
6,90822533
edited Nov 14 '18 at 3:03
gkrogers
6,90822533
6,90822533
asked Nov 14 '18 at 2:32
user10649461user10649461
6
asked Nov 14 '18 at 2:32
user10649461user10649461
6
asked Nov 14 '18 at 2:32
user10649461user10649461
6
6
1
What's your dbms? I think you are using sqlserver You need to take a look docs.microsoft.com/zh-tw/sql/t-sql/functions/…
– D-Shih
Nov 14 '18 at 2:34
Just use standard formats for dates -- YYYY-MM-DD.
– Gordon Linoff
Nov 14 '18 at 3:50
add a comment |
1
What's your dbms? I think you are using sqlserver You need to take a look docs.microsoft.com/zh-tw/sql/t-sql/functions/…
– D-Shih
Nov 14 '18 at 2:34
Just use standard formats for dates -- YYYY-MM-DD.
– Gordon Linoff
Nov 14 '18 at 3:50
1
1
What's your dbms? I think you are using sqlserver You need to take a look docs.microsoft.com/zh-tw/sql/t-sql/functions/…
– D-Shih
Nov 14 '18 at 2:34
What's your dbms? I think you are using sqlserver You need to take a look docs.microsoft.com/zh-tw/sql/t-sql/functions/…
– D-Shih
Nov 14 '18 at 2:34
Just use standard formats for dates -- YYYY-MM-DD.
– Gordon Linoff
Nov 14 '18 at 3:50
Just use standard formats for dates -- YYYY-MM-DD.
– Gordon Linoff
Nov 14 '18 at 3:50
add a comment |
2 Answers
2
active
oldest
votes
here date format is day month year
DATEDIFF(month, Convert(datetime,'01/10/2018',103),
Convert(datetime,'30/04/2019',103)) AS 'Month1'
so difference of 10 month(october) and 4(april) month =6
below code date format is month day year
DATEDIFF(month, '10/01/2018','04/30/2019') AS Months2,
so again 10(october) and 4(april) month difference is 6
and the 3rd date format is like 2nd
DATEDIFF(month, '10/02/2018','05/01/2019') AS Months3
so difference between 10(october) 2018 and may(5) 2019 is 7
As a result your query showing output 6,6,7
answered Nov 14 '18 at 2:41
Zaynul Abadin TuhinZaynul Abadin Tuhin
13k21032
add a comment |
The first 6 is the difference (in months) between 01/10/2018 (October 1, 2018) and 30/04/2019 (April 4, 2019). The CONVERT call is to force the second date to be parsed using date format 103 (i.e. British/French, i.e. dd/mm/yyyy), which makes me suspect that your SQL implementation is set up to parse them as some other format (e.g. US: mm/dd/yyyy).
The second 6 is the difference (in months) between 10/01/2018 (October 1, 2018) and 04/30/2019 (April 30, 2019).
The 7 is the difference (in months) between 10/02/2018 (October 2, 2018) and 05/01/2019 (May 1, 2019). The fact that these dates could be parsed as Feb 10 and Jan 5, but aren't, confirms that your SQL implementation is parsing dates in mm/dd/yyyy format.
If you are really passing dates as strings, I recommend using an unambiguous format (e.g. "October 1, 2018"), as it will make the code clearer, and less brittle.
answered Nov 14 '18 at 2:54
gkrogersgkrogers
6,90822533
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%2f53292371%2fwhy-is-datediff-parsing-these-dates-as-dd-mm-yyyy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
here date format is day month year
DATEDIFF(month, Convert(datetime,'01/10/2018',103),
Convert(datetime,'30/04/2019',103)) AS 'Month1'
so difference of 10 month(october) and 4(april) month =6
below code date format is month day year
DATEDIFF(month, '10/01/2018','04/30/2019') AS Months2,
so again 10(october) and 4(april) month difference is 6
and the 3rd date format is like 2nd
DATEDIFF(month, '10/02/2018','05/01/2019') AS Months3
so difference between 10(october) 2018 and may(5) 2019 is 7
As a result your query showing output 6,6,7
answered Nov 14 '18 at 2:41
Zaynul Abadin TuhinZaynul Abadin Tuhin
13k21032
add a comment |
here date format is day month year
DATEDIFF(month, Convert(datetime,'01/10/2018',103),
Convert(datetime,'30/04/2019',103)) AS 'Month1'
so difference of 10 month(october) and 4(april) month =6
below code date format is month day year
DATEDIFF(month, '10/01/2018','04/30/2019') AS Months2,
so again 10(october) and 4(april) month difference is 6
and the 3rd date format is like 2nd
DATEDIFF(month, '10/02/2018','05/01/2019') AS Months3
so difference between 10(october) 2018 and may(5) 2019 is 7
As a result your query showing output 6,6,7
answered Nov 14 '18 at 2:41
Zaynul Abadin TuhinZaynul Abadin Tuhin
13k21032
add a comment |
here date format is day month year
DATEDIFF(month, Convert(datetime,'01/10/2018',103),
Convert(datetime,'30/04/2019',103)) AS 'Month1'
so difference of 10 month(october) and 4(april) month =6
below code date format is month day year
DATEDIFF(month, '10/01/2018','04/30/2019') AS Months2,
so again 10(october) and 4(april) month difference is 6
and the 3rd date format is like 2nd
DATEDIFF(month, '10/02/2018','05/01/2019') AS Months3
so difference between 10(october) 2018 and may(5) 2019 is 7
As a result your query showing output 6,6,7
answered Nov 14 '18 at 2:41
Zaynul Abadin TuhinZaynul Abadin Tuhin
13k21032
here date format is day month year
DATEDIFF(month, Convert(datetime,'01/10/2018',103),
Convert(datetime,'30/04/2019',103)) AS 'Month1'
so difference of 10 month(october) and 4(april) month =6
below code date format is month day year
DATEDIFF(month, '10/01/2018','04/30/2019') AS Months2,
so again 10(october) and 4(april) month difference is 6
and the 3rd date format is like 2nd
DATEDIFF(month, '10/02/2018','05/01/2019') AS Months3
so difference between 10(october) 2018 and may(5) 2019 is 7
As a result your query showing output 6,6,7
answered Nov 14 '18 at 2:41
Zaynul Abadin TuhinZaynul Abadin Tuhin
13k21032
edited Nov 14 '18 at 2:59
answered Nov 14 '18 at 2:41
Zaynul Abadin TuhinZaynul Abadin Tuhin
13k21032
answered Nov 14 '18 at 2:41
Zaynul Abadin TuhinZaynul Abadin Tuhin
13k21032
answered Nov 14 '18 at 2:41
Zaynul Abadin TuhinZaynul Abadin Tuhin
13k21032
13k21032
add a comment |
add a comment |
The first 6 is the difference (in months) between 01/10/2018 (October 1, 2018) and 30/04/2019 (April 4, 2019). The CONVERT call is to force the second date to be parsed using date format 103 (i.e. British/French, i.e. dd/mm/yyyy), which makes me suspect that your SQL implementation is set up to parse them as some other format (e.g. US: mm/dd/yyyy).
The second 6 is the difference (in months) between 10/01/2018 (October 1, 2018) and 04/30/2019 (April 30, 2019).
The 7 is the difference (in months) between 10/02/2018 (October 2, 2018) and 05/01/2019 (May 1, 2019). The fact that these dates could be parsed as Feb 10 and Jan 5, but aren't, confirms that your SQL implementation is parsing dates in mm/dd/yyyy format.
If you are really passing dates as strings, I recommend using an unambiguous format (e.g. "October 1, 2018"), as it will make the code clearer, and less brittle.
answered Nov 14 '18 at 2:54
gkrogersgkrogers
6,90822533
add a comment |
The first 6 is the difference (in months) between 01/10/2018 (October 1, 2018) and 30/04/2019 (April 4, 2019). The CONVERT call is to force the second date to be parsed using date format 103 (i.e. British/French, i.e. dd/mm/yyyy), which makes me suspect that your SQL implementation is set up to parse them as some other format (e.g. US: mm/dd/yyyy).
The second 6 is the difference (in months) between 10/01/2018 (October 1, 2018) and 04/30/2019 (April 30, 2019).
The 7 is the difference (in months) between 10/02/2018 (October 2, 2018) and 05/01/2019 (May 1, 2019). The fact that these dates could be parsed as Feb 10 and Jan 5, but aren't, confirms that your SQL implementation is parsing dates in mm/dd/yyyy format.
If you are really passing dates as strings, I recommend using an unambiguous format (e.g. "October 1, 2018"), as it will make the code clearer, and less brittle.
answered Nov 14 '18 at 2:54
gkrogersgkrogers
6,90822533
add a comment |
The first 6 is the difference (in months) between 01/10/2018 (October 1, 2018) and 30/04/2019 (April 4, 2019). The CONVERT call is to force the second date to be parsed using date format 103 (i.e. British/French, i.e. dd/mm/yyyy), which makes me suspect that your SQL implementation is set up to parse them as some other format (e.g. US: mm/dd/yyyy).
The second 6 is the difference (in months) between 10/01/2018 (October 1, 2018) and 04/30/2019 (April 30, 2019).
The 7 is the difference (in months) between 10/02/2018 (October 2, 2018) and 05/01/2019 (May 1, 2019). The fact that these dates could be parsed as Feb 10 and Jan 5, but aren't, confirms that your SQL implementation is parsing dates in mm/dd/yyyy format.
If you are really passing dates as strings, I recommend using an unambiguous format (e.g. "October 1, 2018"), as it will make the code clearer, and less brittle.
answered Nov 14 '18 at 2:54
gkrogersgkrogers
6,90822533
The first 6 is the difference (in months) between 01/10/2018 (October 1, 2018) and 30/04/2019 (April 4, 2019). The CONVERT call is to force the second date to be parsed using date format 103 (i.e. British/French, i.e. dd/mm/yyyy), which makes me suspect that your SQL implementation is set up to parse them as some other format (e.g. US: mm/dd/yyyy).
The second 6 is the difference (in months) between 10/01/2018 (October 1, 2018) and 04/30/2019 (April 30, 2019).
The 7 is the difference (in months) between 10/02/2018 (October 2, 2018) and 05/01/2019 (May 1, 2019). The fact that these dates could be parsed as Feb 10 and Jan 5, but aren't, confirms that your SQL implementation is parsing dates in mm/dd/yyyy format.
If you are really passing dates as strings, I recommend using an unambiguous format (e.g. "October 1, 2018"), as it will make the code clearer, and less brittle.
answered Nov 14 '18 at 2:54
gkrogersgkrogers
6,90822533
edited Nov 14 '18 at 3:08
answered Nov 14 '18 at 2:54
gkrogersgkrogers
6,90822533
answered Nov 14 '18 at 2:54
gkrogersgkrogers
6,90822533
answered Nov 14 '18 at 2:54
gkrogersgkrogers
6,90822533
6,90822533
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%2f53292371%2fwhy-is-datediff-parsing-these-dates-as-dd-mm-yyyy%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's your dbms? I think you are using sqlserver You need to take a look docs.microsoft.com/zh-tw/sql/t-sql/functions/…
– D-Shih
Nov 14 '18 at 2:34
Just use standard formats for dates -- YYYY-MM-DD.
– Gordon Linoff
Nov 14 '18 at 3:50