Filter View on Year value of Date

Question

I have a view that so far works okay but now I need to add a column that shows the year the document was created and filter to only show documents created after the year 2011 (2012 on-wards). The view is created with the formula of;

SELECT ApplicationAcceptanceDate = ""

The column I am trying to filter on (Created Year) has the format of dd/mm/yyyy hh:mm:ss and I am using @Year to show all the document years value only which works so far. But how do I use the @year formula to filter the year to 2012 on-wards please? Here is an image of the view, its fields and the formula I have tried so far.

Answer 1

Use @ToTime() to convert a field to a date-time value no matter if it is stored in document as a string or a date-time value.

Extend your selection formula to

SELECT ApplicationAcceptanceDate = "" & @Year(@ToTime(CreatedOn)) > 2011

Show the year of creation in column "Created Year" with formula

@Year(@ToTime(CreatedOn))

Remember that you can filter a view only with selection formula, not with column formulas.

Alternatively, you can use @Created. It returns the date-time when the document was created. Your selection formula would be

SELECT ApplicationAcceptanceDate = "" & @Year(@Created) > 2011

then and the column formula

@Year(@Created)

Answer 2

Is the field CreatedOn a text field or a DateTime field? My guess is that it was created as a text field. Even if you change it in the form now, any existing documents will still have the value stored as text. You need to convert the field on all documents to DateTime, either write an agent to do it or use a tool like NoteMan to quickly change the field type.

You could also use @Year(@TextToTime(CreatedOn)), or (if you have text and datetime fields in different documents) @Year(@TextToTime(@text(CreatedOn)))

Answer 3

The image shows that you are editing the Column Formula for the Created Year column. Click into the white space of the view to make sure you are editing the View Selection formula. Then use:

SELECT ApplicationAcceptanceDate = "" & @Year(CreatedOn) > 2011