Date error in query

  • Hi;

    I am trying to get all objects created and or modified during last week, but instead the query returns very old data

    select name, type,type_desc, create_date, modify_date
    from sys.objects
    where create_date between '2018-10-22'and '2018-11-02'
    and type = 'U' or type = 'P' or type = 'fn';

    typetype_descnamecreate_datemodify_date
    FNSQL_SCALAR_FUNCTIONGetFondosPartidasDescripcioneswithAmount2/12/20146/27/2017
    FNSQL_SCALAR_FUNCTIONfnGetTypeFund11/1/201811/1/2018
    FNSQL_SCALAR_FUNCTIONCIntToChar11/1/201811/1/2018
    FNSQL_SCALAR_FUNCTIONCalculateCheckDigitUPC11/1/201811/1/2018
    FNSQL_SCALAR_FUNCTIONSetControlNumber11/2/201811/2/2018
    FNSQL_SCALAR_FUNCTIONfuncAnioFiscal6/6/20126/6/2012
    FNSQL_SCALAR_FUNCTIONfn_diagramobjects11/5/201811/5/2018
    FNSQL_SCALAR_FUNCTIONfnCalcage11/6/201811/6/2018
    FNSQL_SCALAR_FUNCTIONfnCalcage211/7/201811/7/2018
    FNSQL_SCALAR_FUNCTIONGetUserEmails10/31/201410/31/2014
    FNSQL_SCALAR_FUNCTIONGetFondosPartidasDescripciones8/20/20138/22/2013

  • Orestes - Thursday, November 8, 2018 8:38 AM

    Hi;

    I am trying to get all objects created and or modified during last week, but instead the query returns very old data

    select name, type,type_desc, create_date, modify_date
    from sys.objects
    where create_date between '2018-10-22'and '2018-11-02'
    and type = 'U' or type = 'P' or type = 'fn';

    typetype_descnamecreate_datemodify_date
    FNSQL_SCALAR_FUNCTIONGetFondosPartidasDescripcioneswithAmount2/12/20146/27/2017
    FNSQL_SCALAR_FUNCTIONfnGetTypeFund11/1/201811/1/2018
    FNSQL_SCALAR_FUNCTIONCIntToChar11/1/201811/1/2018
    FNSQL_SCALAR_FUNCTIONCalculateCheckDigitUPC11/1/201811/1/2018
    FNSQL_SCALAR_FUNCTIONSetControlNumber11/2/201811/2/2018
    FNSQL_SCALAR_FUNCTIONfuncAnioFiscal6/6/20126/6/2012
    FNSQL_SCALAR_FUNCTIONfn_diagramobjects11/5/201811/5/2018
    FNSQL_SCALAR_FUNCTIONfnCalcage11/6/201811/6/2018
    FNSQL_SCALAR_FUNCTIONfnCalcage211/7/201811/7/2018
    FNSQL_SCALAR_FUNCTIONGetUserEmails10/31/201410/31/2014
    FNSQL_SCALAR_FUNCTIONGetFondosPartidasDescripciones8/20/20138/22/2013

    Your problem is not knowing the precedence of AND and OR in your query.  Try this:

    select [name]
      , [type]
      , [type_desc]
      , [create_date]
      , [modify_date]
    from [sys].[objects]
    where [create_date]
      between '2018-10-22' and '2018-11-02'
      and ([type] = 'U'
        or [type] = 'P'
        or [type] = 'fn');

  • thank you.... you are complete right i do forgot that !!!!

  • Lynn Pettis - Thursday, November 8, 2018 9:00 AM

    Orestes - Thursday, November 8, 2018 8:38 AM

    Hi;

    I am trying to get all objects created and or modified during last week, but instead the query returns very old data

    select name, type,type_desc, create_date, modify_date
    from sys.objects
    where create_date between '2018-10-22'and '2018-11-02'
    and type = 'U' or type = 'P' or type = 'fn';

    typetype_descnamecreate_datemodify_date
    FNSQL_SCALAR_FUNCTIONGetFondosPartidasDescripcioneswithAmount2/12/20146/27/2017
    FNSQL_SCALAR_FUNCTIONfnGetTypeFund11/1/201811/1/2018
    FNSQL_SCALAR_FUNCTIONCIntToChar11/1/201811/1/2018
    FNSQL_SCALAR_FUNCTIONCalculateCheckDigitUPC11/1/201811/1/2018
    FNSQL_SCALAR_FUNCTIONSetControlNumber11/2/201811/2/2018
    FNSQL_SCALAR_FUNCTIONfuncAnioFiscal6/6/20126/6/2012
    FNSQL_SCALAR_FUNCTIONfn_diagramobjects11/5/201811/5/2018
    FNSQL_SCALAR_FUNCTIONfnCalcage11/6/201811/6/2018
    FNSQL_SCALAR_FUNCTIONfnCalcage211/7/201811/7/2018
    FNSQL_SCALAR_FUNCTIONGetUserEmails10/31/201410/31/2014
    FNSQL_SCALAR_FUNCTIONGetFondosPartidasDescripciones8/20/20138/22/2013

    Your problem is not knowing the precedence of AND and OR in your query.  Try this:

    select [name]
      , [type]
      , [type_desc]
      , [create_date]
      , [modify_date]
    from [sys].[objects]
    where [create_date]
      between '2018-10-22' and '2018-11-02'
      and ([type] = 'U'
        or [type] = 'P'
        or [type] = 'fn');

    Tank you, I totally forgot that !!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply