Results 1 to 3 of 3

Thread: How to get the previous invoice date using Brainscript

  1. #1
    New Contributor
    Join Date
    Nov 2016
    Location
    Broomfield
    Posts
    4

    Default How to get the previous invoice date using Brainscript

    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'))
    SlpBroomfield

  2. #2
    Lavastorm Employee stonysmith's Avatar
    Join Date
    Nov 2006
    Location
    Grapevine Tx
    Posts
    799

    Default

    Quote Originally Posted by sheryle_paisley View Post
    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.
    Code:
    ibd='INVOICE.BILL_DATE'
    emit *
    where   date().dateAdjust(-1,"months") <= ibd and ibd <= date()
    This code will be closer to what you asked for.
    Code:
    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.
    Last edited by stonysmith; 02-27-2019 at 03:33 PM.

  3. #3
    New Contributor
    Join Date
    Nov 2016
    Location
    Broomfield
    Posts
    4

    Default

    Thank you! I'll give it a go...
    SlpBroomfield

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •