Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.
Originally posted by: stonysmithOriginally 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.