NetSuite Saved Search Date Formulas

Saved Search Dates for Financial reporting

Year To Date

CASE WHEN {trandate} BETWEEN to_date(TRUNC({today}, ‘YEAR’), ‘MM/DD/YYYY’) AND to_date({today}) THEN {amount} ELSE 0 END

Last Year To Date

CASE WHEN {trandate} BETWEEN to_date(TRUNC(TRUNC({today}, ‘YEAR’)-1,’YEAR’), ‘MM/DD/YYYY’) AND to_date(ADD_MONTHS({today},-12)) THEN {amount} ELSE 0 END

Current Year

CASE WHEN {trandate} BETWEEN to_date(TRUNC({today}, ‘YEAR’), ‘MM/DD/YYYY’) AND to_date(last_day(add_months(trunc(sysdate,’YYYY’), 11))) THEN {amount} ELSE 0 END

Last Year

CASE WHEN {trandate} BETWEEN TO_DATE(TRUNC(TRUNC({today}, ‘YEAR’)-1,’YEAR’), ‘MM/DD/YYYY’) AND TO_DATE(last_day(add_months(TRUNC(trunc({today},’YYYY’)-1,’YYYY’), 11))) THEN {amount} ELSE 0 END

Quarter To Date

CASE WHEN {trandate} BETWEEN to_date(TRUNC({today}, ‘Q’), ‘MM/DD/YYYY’) AND to_date({today}) THEN {amount} ELSE 0 END

Last Quarter To Date

CASE WHEN {trandate} BETWEEN to_date(Trunc(TRUNC({today}, ‘Q’)-1,’Q’), ‘MM/DD/YYYY’) AND to_date(ADD_MONTHS({today},-3)) THEN {amount} ELSE 0 END

Month To Date

CASE WHEN {trandate} BETWEEN to_date(TRUNC({today}, ‘MONTH’), ‘MM/DD/YYYY’) AND to_date({today}) THEN {amount} ELSE 0 END

Last Month To Date

CASE WHEN {trandate} BETWEEN to_date(TRUNC(TRUNC({today}, ‘MONTH’)-1,’MONTH’), ‘MM/DD/YYYY’) AND to_date(ADD_MONTHS({today},-1)) THEN {amount} ELSE 0 END

Current Month

CASE WHEN {trandate} BETWEEN to_date(TRUNC({today}, ‘MONTH’), ‘MM/DD/YYYY’) AND to_date(LAST_DAY{today}) THEN {amount} ELSE 0 END

Last Month

CASE WHEN {custcol_opp_col_servicestatuschangeda} BETWEEN to_date(TRUNC(TRUNC({today}, ‘MONTH’)-1,’MONTH’), ‘MM/DD/YYYY’) AND to_date(LAST_DAY(TRUNC({today}, ‘MONTH’)-1)) THEN {linerate} ELSE 0 END

First Day of this Month

to_date(TRUNC({today}, ‘MONTH’), ‘MM/DD/YYYY’)

First Day of this Month

TRUNC({today}, ‘MONTH’)

Last Day of this month

LAST_DAY(TRUNC({today}, ‘MONTH’))

First Day of last month

to_date(TRUNC(TRUNC({today}, ‘MONTH’)-1,’MONTH’), ‘MM/DD/YYYY’)

Last Day of last month

LAST_DAY(TRUNC({today}, ‘MONTH’)-1)