I am creating a query entity rule where I need to compare one of my ri value whi

I am creating a query entity rule where I need to compare one of my ri value which is date type with the DB column which is datetime type. I am getting error, Cannot apply operator [EQUALS] to field [UpdatedDateTime] when comparing to value [TypedValue[it=7,v=2016-06-17]].

How to achieve this?

OriginalPostID-219192

OriginalPostID-219192

  Discussion posts and replies are publicly visible

  • We can convert the value into date time and compare the values.
    Example:

    fn!todatetime(ri!date) OR
    fn!datetime(fn!year(ri!date),fn!month(ri!date),fn!day(ri!date),-7,0,0,0)

    a!queryFilter(field: "startDateTime",operator: ">",value: fn!todatetime(ri!date))
  • as suggested by rahulg262 you can convert ri to datetime.
  • You can also use cast to convert the date in the query entity rule to date time.
    Example: cast(typeof(now()),ri!dateVariable)
  • I have tried this, it seems not working.
    I have value like

    2016-06-16 11:26:44.310
    2016-06-16 11:27:23.850
    2016-06-16 11:35:50.540
    2016-06-16 12:13:51.710
    2016-06-16 12:20:28.170 in DB, and I am providing value just 2016-06-16.

    The approach you suggested it is giving blank record.
  • the value you are providing should be of date time format not date format.
  • I think the problem is with the ri. Up to my knowledge I think you are saving the datateime value into a text type variable in Appian and I suspect the value format is 'yyyy-MM-dd' instead of 'MM-dd-yyyy'. Please check the format once.

    In any case, you need to make sure the value format as 'MM-dd-yyyy' and then convert the value to datetime by using either of the functions: todatetime(), cast().
    I would recommend you to go for todatetime() function because the execution time will be faster than cast()

    Hope it helps!!
  • I might be wrong, but afaik, I don't think that simply making use of todatetime() or cast() as suggested above will help you here because I can clearly see that you need to filter the data based on the date whereas the data consists of timestamps and these may not match against the filter value because I guess usage of conversion functions on a date will evaluate to the latest time based on the logged in user.

    There could be two solutions here as per my knowledge:

    1. Add one more column in your view which gets the date part from the existing timestamp column and use this to filter the data.
    2. Build a datetime range query filter on the corresponding timestamp column, for instance, you may do as follows and use the same as filter in the queryEntity(). This way you can get all the records associated with a particular date without much bothering about their timestamps.

    a!queryFilter(
    field: "myTimeStampColumn",
    operator: "between",
    value: {datetime(year(ri!date),month(ri!date),day(ri!date),0,0,0),datetime(year(ri!date),month(ri!date),day(ri!date),23,59,59)}
    )
  • Agree with @rajasekharp that there isn't a need to use cast(),typeof() etc as suggested by a practitioner in his comments above, because casting consumes time as it needs to determine the data type of the dataset first and further cast the dataset to the desired data type and obviously this isn't as straight forward as todatetime(). Casting might be extremely helpful here if we are depending on the field in the CDT (for instance, cast(typeof(local!mycdt.timestampField)),<myData>) and are not aware of the type of data before hand.
  • Hi sikhivahans, I also tried below approach and it works.

    userdatetime(year(ri!updatedDate),month(ri!updatedDate),day(ri!updatedDate),hour(time(23,59,59)),minute(time(23,59,59)),second(time(23,59,59)))

    Thanks everyone!!!
  • @ravir Good, userdatetime() can also be used and is another variant of datetime() which returns the value in local timezone.