SQL server date convertion
I am trying to convert a varchar to date using the below code.
SELECT CAST('14/08/2018' as date) --This code does not work
SELECT CAST('09/08/2018' as date) --This code works
It appears that when the day part of the date gets to '13' that is where it starts breaking.Is there a logical explanation for this?
The error given is :
Msg 241, Level 16, State 1, Line 7670
Conversion failed when converting date and/or time from character string.
sql
sql-server tsql edited Nov 21 '18 at 10:16
Yogesh Sharma
32.8k51438
asked Nov 21 '18 at 10:12
Tshepiso SelepeTshepiso Selepe
135
add a comment |
I am trying to convert a varchar to date using the below code.
SELECT CAST('14/08/2018' as date) --This code does not work
SELECT CAST('09/08/2018' as date) --This code works
It appears that when the day part of the date gets to '13' that is where it starts breaking.Is there a logical explanation for this?
The error given is :
Msg 241, Level 16, State 1, Line 7670
Conversion failed when converting date and/or time from character string.
sql
sql-server tsql edited Nov 21 '18 at 10:16
Yogesh Sharma
32.8k51438
asked Nov 21 '18 at 10:12
Tshepiso SelepeTshepiso Selepe
135
1
Looks like your date format is set to MM/DD/YYYY
– jarlh
Nov 21 '18 at 10:15
If you use default date format that won't happen:2018-08-14
– juergen d
Nov 21 '18 at 10:15
add a comment |
I am trying to convert a varchar to date using the below code.
SELECT CAST('14/08/2018' as date) --This code does not work
SELECT CAST('09/08/2018' as date) --This code works
It appears that when the day part of the date gets to '13' that is where it starts breaking.Is there a logical explanation for this?
The error given is :
Msg 241, Level 16, State 1, Line 7670
Conversion failed when converting date and/or time from character string.
sql
sql-server tsql edited Nov 21 '18 at 10:16
Yogesh Sharma
32.8k51438
asked Nov 21 '18 at 10:12
Tshepiso SelepeTshepiso Selepe
135
I am trying to convert a varchar to date using the below code.
SELECT CAST('14/08/2018' as date) --This code does not work
SELECT CAST('09/08/2018' as date) --This code works
It appears that when the day part of the date gets to '13' that is where it starts breaking.Is there a logical explanation for this?
The error given is :
Msg 241, Level 16, State 1, Line 7670
Conversion failed when converting date and/or time from character string.
sql
sql-server tsql sql
sql-server tsql edited Nov 21 '18 at 10:16
Yogesh Sharma
32.8k51438
asked Nov 21 '18 at 10:12
Tshepiso SelepeTshepiso Selepe
135
edited Nov 21 '18 at 10:16
Yogesh Sharma
32.8k51438
asked Nov 21 '18 at 10:12
Tshepiso SelepeTshepiso Selepe
135
edited Nov 21 '18 at 10:16
Yogesh Sharma
32.8k51438
edited Nov 21 '18 at 10:16
Yogesh Sharma
32.8k51438
edited Nov 21 '18 at 10:16
Yogesh Sharma
32.8k51438
32.8k51438
asked Nov 21 '18 at 10:12
Tshepiso SelepeTshepiso Selepe
135
asked Nov 21 '18 at 10:12
Tshepiso SelepeTshepiso Selepe
135
asked Nov 21 '18 at 10:12
Tshepiso SelepeTshepiso Selepe
135
135
1
Looks like your date format is set to MM/DD/YYYY
– jarlh
Nov 21 '18 at 10:15
If you use default date format that won't happen:2018-08-14
– juergen d
Nov 21 '18 at 10:15
add a comment |
1
Looks like your date format is set to MM/DD/YYYY
– jarlh
Nov 21 '18 at 10:15
If you use default date format that won't happen:2018-08-14
– juergen d
Nov 21 '18 at 10:15
1
1
Looks like your date format is set to MM/DD/YYYY
– jarlh
Nov 21 '18 at 10:15
Looks like your date format is set to MM/DD/YYYY
– jarlh
Nov 21 '18 at 10:15
If you use default date format that won't happen:
2018-08-14– juergen d
Nov 21 '18 at 10:15
If you use default date format that won't happen:
2018-08-14– juergen d
Nov 21 '18 at 10:15
add a comment |
2 Answers
2
active
oldest
votes
You should decide your date component before conversation :
I would considered date with style dd/mm/yyyy :
SELECT CONVERT(DATE, '14/08/2018', 103)
However, it seems SQL has set date mm/dd/yyyy.
If so, you can change it :
set dateformat dmy
answered Nov 21 '18 at 10:14
Yogesh SharmaYogesh Sharma
32.8k51438
Thanks a lot this has solved my issue.
– Tshepiso Selepe
Nov 21 '18 at 10:34
@TshepisoSelepe if this answer solved your problem, then please mark it as the correct answer by clicking on the check mark at the left.
– Lajos Arpad
Nov 21 '18 at 11:00
Done.Thank you very much
– Tshepiso Selepe
Nov 30 '18 at 8:05
add a comment |
The cause of your problem is that you have a mm/dd/yyyy format and the first value is the month. You will either need to swap the first and the second value. You can use convert for this purpose with option 101, which converts a mm/dd/yyyy to a mm/dd/yyyy:
select convert(DATE, '14/08/2018', 101);
This is the US standard and this converts your varchar to a DATE. Now, if you want to display this in a format of dd/mm/yyyy, then just do
select convert(varchar(10), convert(DATE, '14/08/2018', 101), 103);
answered Nov 21 '18 at 10:25
Lajos ArpadLajos Arpad
28.4k1861118
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%2f53409715%2fsql-server-date-convertion%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
You should decide your date component before conversation :
I would considered date with style dd/mm/yyyy :
SELECT CONVERT(DATE, '14/08/2018', 103)
However, it seems SQL has set date mm/dd/yyyy.
If so, you can change it :
set dateformat dmy
answered Nov 21 '18 at 10:14
Yogesh SharmaYogesh Sharma
32.8k51438
Thanks a lot this has solved my issue.
– Tshepiso Selepe
Nov 21 '18 at 10:34
@TshepisoSelepe if this answer solved your problem, then please mark it as the correct answer by clicking on the check mark at the left.
– Lajos Arpad
Nov 21 '18 at 11:00
Done.Thank you very much
– Tshepiso Selepe
Nov 30 '18 at 8:05
add a comment |
You should decide your date component before conversation :
I would considered date with style dd/mm/yyyy :
SELECT CONVERT(DATE, '14/08/2018', 103)
However, it seems SQL has set date mm/dd/yyyy.
If so, you can change it :
set dateformat dmy
answered Nov 21 '18 at 10:14
Yogesh SharmaYogesh Sharma
32.8k51438
Thanks a lot this has solved my issue.
– Tshepiso Selepe
Nov 21 '18 at 10:34
@TshepisoSelepe if this answer solved your problem, then please mark it as the correct answer by clicking on the check mark at the left.
– Lajos Arpad
Nov 21 '18 at 11:00
Done.Thank you very much
– Tshepiso Selepe
Nov 30 '18 at 8:05
add a comment |
You should decide your date component before conversation :
I would considered date with style dd/mm/yyyy :
SELECT CONVERT(DATE, '14/08/2018', 103)
However, it seems SQL has set date mm/dd/yyyy.
If so, you can change it :
set dateformat dmy
answered Nov 21 '18 at 10:14
Yogesh SharmaYogesh Sharma
32.8k51438
You should decide your date component before conversation :
I would considered date with style dd/mm/yyyy :
SELECT CONVERT(DATE, '14/08/2018', 103)
However, it seems SQL has set date mm/dd/yyyy.
If so, you can change it :
set dateformat dmy
answered Nov 21 '18 at 10:14
Yogesh SharmaYogesh Sharma
32.8k51438
edited Nov 21 '18 at 10:23
answered Nov 21 '18 at 10:14
Yogesh SharmaYogesh Sharma
32.8k51438
answered Nov 21 '18 at 10:14
Yogesh SharmaYogesh Sharma
32.8k51438
answered Nov 21 '18 at 10:14
Yogesh SharmaYogesh Sharma
32.8k51438
32.8k51438
Thanks a lot this has solved my issue.
– Tshepiso Selepe
Nov 21 '18 at 10:34
@TshepisoSelepe if this answer solved your problem, then please mark it as the correct answer by clicking on the check mark at the left.
– Lajos Arpad
Nov 21 '18 at 11:00
Done.Thank you very much
– Tshepiso Selepe
Nov 30 '18 at 8:05
add a comment |
Thanks a lot this has solved my issue.
– Tshepiso Selepe
Nov 21 '18 at 10:34
@TshepisoSelepe if this answer solved your problem, then please mark it as the correct answer by clicking on the check mark at the left.
– Lajos Arpad
Nov 21 '18 at 11:00
Done.Thank you very much
– Tshepiso Selepe
Nov 30 '18 at 8:05
Thanks a lot this has solved my issue.
– Tshepiso Selepe
Nov 21 '18 at 10:34
Thanks a lot this has solved my issue.
– Tshepiso Selepe
Nov 21 '18 at 10:34
@TshepisoSelepe if this answer solved your problem, then please mark it as the correct answer by clicking on the check mark at the left.
– Lajos Arpad
Nov 21 '18 at 11:00
@TshepisoSelepe if this answer solved your problem, then please mark it as the correct answer by clicking on the check mark at the left.
– Lajos Arpad
Nov 21 '18 at 11:00
Done.Thank you very much
– Tshepiso Selepe
Nov 30 '18 at 8:05
Done.Thank you very much
– Tshepiso Selepe
Nov 30 '18 at 8:05
add a comment |
The cause of your problem is that you have a mm/dd/yyyy format and the first value is the month. You will either need to swap the first and the second value. You can use convert for this purpose with option 101, which converts a mm/dd/yyyy to a mm/dd/yyyy:
select convert(DATE, '14/08/2018', 101);
This is the US standard and this converts your varchar to a DATE. Now, if you want to display this in a format of dd/mm/yyyy, then just do
select convert(varchar(10), convert(DATE, '14/08/2018', 101), 103);
answered Nov 21 '18 at 10:25
Lajos ArpadLajos Arpad
28.4k1861118
add a comment |
The cause of your problem is that you have a mm/dd/yyyy format and the first value is the month. You will either need to swap the first and the second value. You can use convert for this purpose with option 101, which converts a mm/dd/yyyy to a mm/dd/yyyy:
select convert(DATE, '14/08/2018', 101);
This is the US standard and this converts your varchar to a DATE. Now, if you want to display this in a format of dd/mm/yyyy, then just do
select convert(varchar(10), convert(DATE, '14/08/2018', 101), 103);
answered Nov 21 '18 at 10:25
Lajos ArpadLajos Arpad
28.4k1861118
add a comment |
The cause of your problem is that you have a mm/dd/yyyy format and the first value is the month. You will either need to swap the first and the second value. You can use convert for this purpose with option 101, which converts a mm/dd/yyyy to a mm/dd/yyyy:
select convert(DATE, '14/08/2018', 101);
This is the US standard and this converts your varchar to a DATE. Now, if you want to display this in a format of dd/mm/yyyy, then just do
select convert(varchar(10), convert(DATE, '14/08/2018', 101), 103);
answered Nov 21 '18 at 10:25
Lajos ArpadLajos Arpad
28.4k1861118
The cause of your problem is that you have a mm/dd/yyyy format and the first value is the month. You will either need to swap the first and the second value. You can use convert for this purpose with option 101, which converts a mm/dd/yyyy to a mm/dd/yyyy:
select convert(DATE, '14/08/2018', 101);
This is the US standard and this converts your varchar to a DATE. Now, if you want to display this in a format of dd/mm/yyyy, then just do
select convert(varchar(10), convert(DATE, '14/08/2018', 101), 103);
answered Nov 21 '18 at 10:25
Lajos ArpadLajos Arpad
28.4k1861118
answered Nov 21 '18 at 10:25
Lajos ArpadLajos Arpad
28.4k1861118
answered Nov 21 '18 at 10:25
Lajos ArpadLajos Arpad
28.4k1861118
answered Nov 21 '18 at 10:25
Lajos ArpadLajos Arpad
28.4k1861118
28.4k1861118
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%2f53409715%2fsql-server-date-convertion%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
Looks like your date format is set to MM/DD/YYYY
– jarlh
Nov 21 '18 at 10:15
If you use default date format that won't happen:
2018-08-14– juergen d
Nov 21 '18 at 10:15