22 Sep 2009
11 Apr 2011
Link to this post
I've added these metaFields to my a Documents & Images (v3.7 build 2096) module:
These fields are for storing effective dates for a document (i.e. "This document is valid from 1/1/2011 - 12/31/2011") The fields are intended to be optional: A document with no ToDate is always valid.
When displaying information from this module on my site I'd like to only show entries where "#Now" is between FromDate and ToDate OR where ToDate IS NULL OR where ToDate = "". If I were constructing a SQL statement to achieve this, it would be: " ... WHERE isnull(FromDate, getdate()) >= getdate() AND isnull(ToDate, getdate() <= getdate())"
I've got this FilterExpression: ParentID IN (4e4e997a-43ad-4e39-9aca-43c8c80b540a) AND FromDate <= "#Now" AND ToDate >= "#Now"
It works, other than the case where FromDate or ToDate is not populated.
How can I achieve the "or is null" logic I'm after? SiteFinity seems to not like parenthesis in a FilterExpression and I can't find adequate documentation on this in the User Manual, Forums, Blog posts, etc.
Further question: Is "ToDate IS NULL OR ToDate = """ redundant? Does blank = NULL?