Converting date and/or time from character string Conversion failed

Multi tool use
Multi tool use





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







-1















declare @orderwhere varchar(5000)
declare @Pdate DateTime

set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate=' + @Pdate

exec('select Product.ProductID
from Product
where ' + @orderwhere)


I get these errors:




Msg 241, Level 16, State 1, Line 4

Conversion failed when converting date and/or time from character string.











share|improve this question




















  • 2





    Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.

    – sticky bit
    Jan 3 at 19:23











  • Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.

    – scsimon
    Jan 3 at 19:29


















-1















declare @orderwhere varchar(5000)
declare @Pdate DateTime

set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate=' + @Pdate

exec('select Product.ProductID
from Product
where ' + @orderwhere)


I get these errors:




Msg 241, Level 16, State 1, Line 4

Conversion failed when converting date and/or time from character string.











share|improve this question




















  • 2





    Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.

    – sticky bit
    Jan 3 at 19:23











  • Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.

    – scsimon
    Jan 3 at 19:29














-1












-1








-1








declare @orderwhere varchar(5000)
declare @Pdate DateTime

set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate=' + @Pdate

exec('select Product.ProductID
from Product
where ' + @orderwhere)


I get these errors:




Msg 241, Level 16, State 1, Line 4

Conversion failed when converting date and/or time from character string.











share|improve this question
















declare @orderwhere varchar(5000)
declare @Pdate DateTime

set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate=' + @Pdate

exec('select Product.ProductID
from Product
where ' + @orderwhere)


I get these errors:




Msg 241, Level 16, State 1, Line 4

Conversion failed when converting date and/or time from character string.








sql-server






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 3 at 21:41









marc_s

584k13011241270




584k13011241270










asked Jan 3 at 18:52









Ritesh PatelRitesh Patel

95




95








  • 2





    Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.

    – sticky bit
    Jan 3 at 19:23











  • Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.

    – scsimon
    Jan 3 at 19:29














  • 2





    Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.

    – sticky bit
    Jan 3 at 19:23











  • Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.

    – scsimon
    Jan 3 at 19:29








2




2





Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.

– sticky bit
Jan 3 at 19:23





Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.

– sticky bit
Jan 3 at 19:23













Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.

– scsimon
Jan 3 at 19:29





Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.

– scsimon
Jan 3 at 19:29












3 Answers
3






active

oldest

votes


















1














Edit:



Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01' and the error message "Conversion failed when converting date and/or time from character string." in it.





From the manual for datetime:




Date range[:] January 1, 1753, through December 31, 9999




So your year is definitely to early for a datetime.



You can try to use either a datetime2, which ranges from 0001-01-01 through 9999-12-31 or just a date with the same range as datetime2 (regarding the date part, not the time part of course).






share|improve this answer


























  • They edited the question with a different error, thought his answers the original posted error.

    – scsimon
    Jan 3 at 19:21





















0














You have to convert your @pdate to varchar



declare @orderwhere varchar(5000)
declare @Pdate DateTime
declare @Pdatec varchar(50)
set @Pdatec = CAST(@Pdate as varchar(50))


set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''

exec('select Product.ProductID
from Product
where ' + @orderwhere)


and add quotation marks.
Try to test your dynamic queries by printing your results.






share|improve this answer



















  • 1





    scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.

    – Mathew Paxinos
    Jan 3 at 21:56



















0














You need to convert the @Pdate to varchar for the string concatenation.



set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''


Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT the statement for debugging before you execute.



Example:



declare @orderwhere varchar(5000)
declare @Pdate date = '20160101'

set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''

print('select Product.ProductID
from Product
where ' + @orderwhere)


Or, using a datetime



declare @orderwhere varchar(5000)
declare @Pdate datetime = '20160101'

set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''

print('select Product.ProductID
from Product
where ' + @orderwhere)





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


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54028171%2fconverting-date-and-or-time-from-character-string-conversion-failed%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    Edit:



    Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01' and the error message "Conversion failed when converting date and/or time from character string." in it.





    From the manual for datetime:




    Date range[:] January 1, 1753, through December 31, 9999




    So your year is definitely to early for a datetime.



    You can try to use either a datetime2, which ranges from 0001-01-01 through 9999-12-31 or just a date with the same range as datetime2 (regarding the date part, not the time part of course).






    share|improve this answer


























    • They edited the question with a different error, thought his answers the original posted error.

      – scsimon
      Jan 3 at 19:21


















    1














    Edit:



    Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01' and the error message "Conversion failed when converting date and/or time from character string." in it.





    From the manual for datetime:




    Date range[:] January 1, 1753, through December 31, 9999




    So your year is definitely to early for a datetime.



    You can try to use either a datetime2, which ranges from 0001-01-01 through 9999-12-31 or just a date with the same range as datetime2 (regarding the date part, not the time part of course).






    share|improve this answer


























    • They edited the question with a different error, thought his answers the original posted error.

      – scsimon
      Jan 3 at 19:21
















    1












    1








    1







    Edit:



    Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01' and the error message "Conversion failed when converting date and/or time from character string." in it.





    From the manual for datetime:




    Date range[:] January 1, 1753, through December 31, 9999




    So your year is definitely to early for a datetime.



    You can try to use either a datetime2, which ranges from 0001-01-01 through 9999-12-31 or just a date with the same range as datetime2 (regarding the date part, not the time part of course).






    share|improve this answer















    Edit:



    Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01' and the error message "Conversion failed when converting date and/or time from character string." in it.





    From the manual for datetime:




    Date range[:] January 1, 1753, through December 31, 9999




    So your year is definitely to early for a datetime.



    You can try to use either a datetime2, which ranges from 0001-01-01 through 9999-12-31 or just a date with the same range as datetime2 (regarding the date part, not the time part of course).







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Jan 3 at 19:25

























    answered Jan 3 at 19:15









    sticky bitsticky bit

    16k111733




    16k111733













    • They edited the question with a different error, thought his answers the original posted error.

      – scsimon
      Jan 3 at 19:21





















    • They edited the question with a different error, thought his answers the original posted error.

      – scsimon
      Jan 3 at 19:21



















    They edited the question with a different error, thought his answers the original posted error.

    – scsimon
    Jan 3 at 19:21







    They edited the question with a different error, thought his answers the original posted error.

    – scsimon
    Jan 3 at 19:21















    0














    You have to convert your @pdate to varchar



    declare @orderwhere varchar(5000)
    declare @Pdate DateTime
    declare @Pdatec varchar(50)
    set @Pdatec = CAST(@Pdate as varchar(50))


    set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''

    exec('select Product.ProductID
    from Product
    where ' + @orderwhere)


    and add quotation marks.
    Try to test your dynamic queries by printing your results.






    share|improve this answer



















    • 1





      scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.

      – Mathew Paxinos
      Jan 3 at 21:56
















    0














    You have to convert your @pdate to varchar



    declare @orderwhere varchar(5000)
    declare @Pdate DateTime
    declare @Pdatec varchar(50)
    set @Pdatec = CAST(@Pdate as varchar(50))


    set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''

    exec('select Product.ProductID
    from Product
    where ' + @orderwhere)


    and add quotation marks.
    Try to test your dynamic queries by printing your results.






    share|improve this answer



















    • 1





      scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.

      – Mathew Paxinos
      Jan 3 at 21:56














    0












    0








    0







    You have to convert your @pdate to varchar



    declare @orderwhere varchar(5000)
    declare @Pdate DateTime
    declare @Pdatec varchar(50)
    set @Pdatec = CAST(@Pdate as varchar(50))


    set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''

    exec('select Product.ProductID
    from Product
    where ' + @orderwhere)


    and add quotation marks.
    Try to test your dynamic queries by printing your results.






    share|improve this answer













    You have to convert your @pdate to varchar



    declare @orderwhere varchar(5000)
    declare @Pdate DateTime
    declare @Pdatec varchar(50)
    set @Pdatec = CAST(@Pdate as varchar(50))


    set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''

    exec('select Product.ProductID
    from Product
    where ' + @orderwhere)


    and add quotation marks.
    Try to test your dynamic queries by printing your results.







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Jan 3 at 19:22









    Alvaro ParraAlvaro Parra

    6091722




    6091722








    • 1





      scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.

      – Mathew Paxinos
      Jan 3 at 21:56














    • 1





      scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.

      – Mathew Paxinos
      Jan 3 at 21:56








    1




    1





    scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.

    – Mathew Paxinos
    Jan 3 at 21:56





    scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.

    – Mathew Paxinos
    Jan 3 at 21:56











    0














    You need to convert the @Pdate to varchar for the string concatenation.



    set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''


    Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT the statement for debugging before you execute.



    Example:



    declare @orderwhere varchar(5000)
    declare @Pdate date = '20160101'

    set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''

    print('select Product.ProductID
    from Product
    where ' + @orderwhere)


    Or, using a datetime



    declare @orderwhere varchar(5000)
    declare @Pdate datetime = '20160101'

    set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''

    print('select Product.ProductID
    from Product
    where ' + @orderwhere)





    share|improve this answer






























      0














      You need to convert the @Pdate to varchar for the string concatenation.



      set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''


      Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT the statement for debugging before you execute.



      Example:



      declare @orderwhere varchar(5000)
      declare @Pdate date = '20160101'

      set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''

      print('select Product.ProductID
      from Product
      where ' + @orderwhere)


      Or, using a datetime



      declare @orderwhere varchar(5000)
      declare @Pdate datetime = '20160101'

      set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''

      print('select Product.ProductID
      from Product
      where ' + @orderwhere)





      share|improve this answer




























        0












        0








        0







        You need to convert the @Pdate to varchar for the string concatenation.



        set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''


        Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT the statement for debugging before you execute.



        Example:



        declare @orderwhere varchar(5000)
        declare @Pdate date = '20160101'

        set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''

        print('select Product.ProductID
        from Product
        where ' + @orderwhere)


        Or, using a datetime



        declare @orderwhere varchar(5000)
        declare @Pdate datetime = '20160101'

        set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''

        print('select Product.ProductID
        from Product
        where ' + @orderwhere)





        share|improve this answer















        You need to convert the @Pdate to varchar for the string concatenation.



        set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''


        Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT the statement for debugging before you execute.



        Example:



        declare @orderwhere varchar(5000)
        declare @Pdate date = '20160101'

        set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''

        print('select Product.ProductID
        from Product
        where ' + @orderwhere)


        Or, using a datetime



        declare @orderwhere varchar(5000)
        declare @Pdate datetime = '20160101'

        set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''

        print('select Product.ProductID
        from Product
        where ' + @orderwhere)






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Jan 3 at 19:22

























        answered Jan 3 at 19:15









        scsimonscsimon

        22.3k51737




        22.3k51737






























            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














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54028171%2fconverting-date-and-or-time-from-character-string-conversion-failed%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







            EjNhewlKKFFzTV7
            Y0aD JAqVLPXXk4bOJURPZB9yEtwI K,8U8Bk7eU SzL,vB TAx,RLDynE8lzdu AiG4nWnIN v 8T6,F,dQxZP p,vNDPzR9IVk

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas