Filtering Date and Time record fields by year

Question

I am working on the Online Academy courses to create an expression that will return a count of vehicles for a specific year. 
 I am finding it really difficult to work with the date and time functions. What is the best way to filter a date and time record field by the year? 
 Any time I try to cast or work with a date time field I get an error. 
 With the example below, I get, 
 "Expression evaluation error at function a!queryRecordType: Unable to find a required instance of TypedValue." 
 With other functions (date, todate, gmt etc), I get errors saying that the field can't be converted to a date and time with timezone even though the field is a date and time field.

a!queryRecordType(
recordType: 'recordType!{99b0bb09-e2c0-4f75-a70b-26f247e2d095}AA Vehicle',
fields: a!aggregationFields(
groupings: {
a!grouping(
field: 'recordType!{99b0bb09-e2c0-4f75-a70b-26f247e2d095}AA Vehicle.fields.{3f0958cd-8b0d-4071-afdf-fa0902a40002}createdOn',
alias: "yearCreatedOn",
interval: "YEAR"
)
},
measures: {
a!measure(
function: "COUNT",
field: 'recordType!{99b0bb09-e2c0-4f75-a70b-26f247e2d095}AA Vehicle.fields.{3f0958cd-8b0d-4071-afdf-fa0902a40002}createdOn',
alias: "count"
)
}
),
filters: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: datetext('recordType!{99b0bb09-e2c0-4f75-a70b-26f247e2d095}AA Vehicle.fields.{3f0958cd-8b0d-4071-afdf-fa0902a40002}createdOn',"MM/DD/YYYY"),
operator: "=",
value: ri!inputYear
)
},
ignoreFiltersWithEmptyValues: true
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 100
)
).data

Mudit_Sharma · Answer

Hi andrzejr3338 , If you first generate year start date time and year end date time based on the year as the input, you can apply "between" operator on date time field. You can refer the below code. a!localVariables(
 local!yearStartDateTime: datetime(ri!inputYear, 01, 01, 00, 00, 00, 00),
 local!yearEndDateTime: datetime(ri!inputYear, 12, 31, 23, 59, 59, 00),
 a!queryRecordType(
 recordType: 'recordType!{99b0bb09-e2c0-4f75-a70b-26f247e2d095}AA Vehicle',
 fields: a!aggregationFields(
 groupings: {
 a!grouping(
 field: 'recordType!{99b0bb09-e2c0-4f75-a70b-26f247e2d095}AA Vehicle.fields.{3f0958cd-8b0d-4071-afdf-fa0902a40002}createdOn',
 alias: "yearCreatedOn",
 interval: "YEAR"
 )
 },
 measures: {
 a!measure(
 function: "COUNT",
 field: 'recordType!{99b0bb09-e2c0-4f75-a70b-26f247e2d095}AA Vehicle.fields.{0d5995ff-5e2d-40ad-8ce7-1dc2ebe91919}vehicleId',
 alias: "count"
 )
 }
 ),
 filters: a!queryLogicalExpression(
 operator: "AND",
 filters: {
 a!queryFilter(
 field: 'recordType!{99b0bb09-e2c0-4f75-a70b-26f247e2d095}AA Vehicle.fields.{3f0958cd-8b0d-4071-afdf-fa0902a40002}createdOn',
 operator: "between",
 value: {
 local!yearStartDateTime,
 local!yearEndDateTime
 }
 )
 },
 ignoreFiltersWithEmptyValues: true
 ),
 pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
 ).data
) There could be other approaches also, but this one works fine. Hope this answers your question.

Harshit Bumb (Appyzie) · Answer

I assume you are working on the records. The best way to do so is to create a custom record field where you extract the year in a separate column and then filter on that column in your queryRecordType

Filtering Date and Time record fields by year

Top Replies