We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

How to get the previous invoice date using Brainscript

Comments

2 comments

  • Avatar
    Infogix Support
    Originally posted by: stonysmith

    Originally posted by: sheryle_paisley
    					

    Does anyone know how to translate this sql to BrainScript?

    (to_char(INVOICE.BILL_DATE,'yyyy/mm')>=to_char(add_months(sysdate,-1),'yyyy/mm') and to_char(INVOICE.BILL_DATE,'yyyy/mm')<=to_char(sysdate,'yyyy/mm'))
    to_char => use either str() or format()
    sysdate => use date() without any parameters to get "today"
    add_months => use dateAdjust(field,-1,"months")

    This code will be similiar, but it takes into account the DAY of the bill also. if you only want month(s), see below.
    ibd='INVOICE.BILL_DATE'
    emit *
    where   date().dateAdjust(-1,"months") <= ibd and ibd <= date()
    This code will be closer to what you asked for.
    ibd='INVOICE.BILL_DATE'
    ibd = date(ibd.year(),ibd.month(),1) #force to first of month
    prevmonth =date().dateAdjust(-1,"months")
    prevmonth =date(prevmonth.year(),prevmonth.month(),1)  #forces first of last month
    emit *
    where   prevmonth <= ibd and ibd <= date()
    Note that in both cases, I left the IBD as a date .. no real need to convert either side to strings.
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    Originally posted by: sheryle_paisley

    Thank you! I'll give it a go...
    0
    Comment actions Permalink

Please sign in to leave a comment.