Skip to main content


search shadow

Adding Custom Database Functions or Fields



  • Jeremy Mapes
    Jeremy Mapes

    There are no examples of of the content that might be used in the filter. I have tried to copy simple SQL statements and this fails every time. For example, maybe I want the year from @LASTMONTH. I would assume it to be SELECT DATEPART(year, @LASTMONTH)
    but that doesn't seem to work. Examples would help.

  • Tony Coffman
    Tony Coffman

    Hello Jeremy,

    Database Functions code is sent directly to the database to evaluate. @LastMonth is not a valid expression to send to a SQL or Cache database, @LastMonth is a reserved word for VDM used specifically for filtering. To get the results from last month your database function would return a date value. You'd then apply a filter for the database function where it is equal to @LastMonth.

  • Robert Richardson
    Robert Richardson

    I agree - having documentation to reference re: commonly used syntax would be very handy. (Cache).

  • Tony Coffman
    Tony Coffman

    Hello Robert,

    Jeremy's question was related to Reserved Words and Special Data Filtering Options. These are processed locally and then sent to the database as actual dates. Additional documentation and examples of Special Date Filter Options is available here.

    As for supported Cache specific ODBC functions. Please check out the link below on InterSystems website. Custom Database Functions and Field's syntax are passed directly to the database for evaluation and processing. So any supported function for a database platform is available, and varies by the provider (MS SQL, Oracle etc).


Please sign in to leave a comment.