none
Query executes on one version but gives error on other RRS feed

  • Question

  • Hi,

    I have a select query which is fine on SQL server version (12.0.5546.0) but gives following error on version (12.0.5207.0).

    Need quick help.

    Msg 241, Level 16, State 1, Procedure rpt_FLASH_REPORT_CODE, Line 27
    Conversion failed when converting date and/or time from character string.
    Warning: Null value is eliminated by an aggregate or other SET operation.

    Thanks

    Thursday, March 15, 2018 12:21 PM

Answers

  • The issue is with your servers language and regional settings

    When you pass date values through strings it gets interpreted based on language settings of your server

    So a date value in MM/dd/yyyy wont work well in British_English for example as it interprets it in dd/MM/yyyy format so value of say 10/13/2017 (13th Oct 2017) will be invalid in case of UK setting as 13 will be interpreted as month which is invalid

    So solution is to pass the values always in unambiguous ISO format (yyyyMMdd)

    I've explained it in detail here with illustration

    https://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ZahidButt Thursday, March 15, 2018 2:44 PM
    Thursday, March 15, 2018 12:56 PM

All replies

  • Please send the query
    Thursday, March 15, 2018 12:26 PM
  • It doesn't look that is related with the version. Check if the column of the two tables have the same DDL. 
    Thursday, March 15, 2018 12:36 PM
  • The issue is with your servers language and regional settings

    When you pass date values through strings it gets interpreted based on language settings of your server

    So a date value in MM/dd/yyyy wont work well in British_English for example as it interprets it in dd/MM/yyyy format so value of say 10/13/2017 (13th Oct 2017) will be invalid in case of UK setting as 13 will be interpreted as month which is invalid

    So solution is to pass the values always in unambiguous ISO format (yyyyMMdd)

    I've explained it in detail here with illustration

    https://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ZahidButt Thursday, March 15, 2018 2:44 PM
    Thursday, March 15, 2018 12:56 PM
  • Most likely this is due to that you have bad data. Depending on the execution plans, the bad data may be filtered out before the conversion is reached. Make sure that you use try_convert for theese conversions, so that you get NULL back rather an error.

    The SQL Server version as such has nothing to do with it.

    Thursday, March 15, 2018 1:58 PM
  • Thanks Visakh16,

    It was a format issue, I used convert(varchar,mydate,111) & its resolved.

    Thanks every one.

    Thursday, March 15, 2018 2:46 PM