SQL server date convertion











up vote
2
down vote

favorite












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







share|improve this question




















  • 1




    Looks like your date format is set to MM/DD/YYYY
    – jarlh
    2 days ago










  • If you use default date format that won't happen: 2018-08-14
    – juergen d
    2 days ago















up vote
2
down vote

favorite












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







share|improve this question




















  • 1




    Looks like your date format is set to MM/DD/YYYY
    – jarlh
    2 days ago










  • If you use default date format that won't happen: 2018-08-14
    – juergen d
    2 days ago













up vote
2
down vote

favorite









up vote
2
down vote

favorite











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







share|improve this question















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 2 days ago









Yogesh Sharma

26.6k51334




26.6k51334










asked 2 days ago









Tshepiso Selepe

114




114








  • 1




    Looks like your date format is set to MM/DD/YYYY
    – jarlh
    2 days ago










  • If you use default date format that won't happen: 2018-08-14
    – juergen d
    2 days ago














  • 1




    Looks like your date format is set to MM/DD/YYYY
    – jarlh
    2 days ago










  • If you use default date format that won't happen: 2018-08-14
    – juergen d
    2 days ago








1




1




Looks like your date format is set to MM/DD/YYYY
– jarlh
2 days ago




Looks like your date format is set to MM/DD/YYYY
– jarlh
2 days ago












If you use default date format that won't happen: 2018-08-14
– juergen d
2 days ago




If you use default date format that won't happen: 2018-08-14
– juergen d
2 days ago












2 Answers
2






active

oldest

votes

















up vote
2
down vote













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





share|improve this answer























  • Thanks a lot this has solved my issue.
    – Tshepiso Selepe
    2 days ago










  • @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
    2 days ago


















up vote
1
down vote













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);





share|improve this answer





















    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',
    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


















    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








    up vote
    2
    down vote













    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





    share|improve this answer























    • Thanks a lot this has solved my issue.
      – Tshepiso Selepe
      2 days ago










    • @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
      2 days ago















    up vote
    2
    down vote













    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





    share|improve this answer























    • Thanks a lot this has solved my issue.
      – Tshepiso Selepe
      2 days ago










    • @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
      2 days ago













    up vote
    2
    down vote










    up vote
    2
    down vote









    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





    share|improve this answer














    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






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited 2 days ago

























    answered 2 days ago









    Yogesh Sharma

    26.6k51334




    26.6k51334












    • Thanks a lot this has solved my issue.
      – Tshepiso Selepe
      2 days ago










    • @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
      2 days ago


















    • Thanks a lot this has solved my issue.
      – Tshepiso Selepe
      2 days ago










    • @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
      2 days ago
















    Thanks a lot this has solved my issue.
    – Tshepiso Selepe
    2 days ago




    Thanks a lot this has solved my issue.
    – Tshepiso Selepe
    2 days ago












    @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
    2 days ago




    @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
    2 days ago












    up vote
    1
    down vote













    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);





    share|improve this answer

























      up vote
      1
      down vote













      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);





      share|improve this answer























        up vote
        1
        down vote










        up vote
        1
        down vote









        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);





        share|improve this answer












        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);






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 2 days ago









        Lajos Arpad

        26.9k1861116




        26.9k1861116






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            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





















































            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







            -