Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
+1
person also asked this
people also asked this
Replies
14 replies
Subscribers
8 subscribers
Views
12334 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
AI and Rules
I am creating a query entity rule where I need to compare one of my ri value whi
Ravi Roshan
over 8 years ago
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
0
Rahul Gundu
over 8 years ago
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))
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sparshs
over 8 years ago
as suggested by rahulg262 you can convert ri to datetime.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Sidhant Behura
over 8 years ago
You can also use cast to convert the date in the query entity rule to date time.
Example: cast(typeof(now()),ri!dateVariable)
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Ravi Roshan
over 8 years ago
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.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sparshs
over 8 years ago
the value you are providing should be of date time format not date format.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Rajasekhar Pamidipati
A Score Level 2
over 8 years ago
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!!
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sikhivahans
over 8 years ago
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)}
)
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sikhivahans
over 8 years ago
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.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Ravi Roshan
over 8 years ago
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!!!
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sikhivahans
over 8 years ago
@ravir Good, userdatetime() can also be used and is another variant of datetime() which returns the value in local timezone.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
>