Solved

Expression to convert Datetime to End of Day

  • 16 July 2021
  • 2 replies
  • 474 views
J
Rank
Userlevel 2
Badge +5

Hi all,

 

I have an XML Business Rule to create TIME_COMMIT associated with a Task. I have a USER_DEF_DTTM that has the start DateTime for the TIME_COMMIT and I need to calculate the end of the day of that DateTime to store it at the end of the TIME_COMMIT (commit_dttm).

 

The XML that I’m using is the following:

<update_time_commit>
 <time_commit>
  <task_id>@task_id</task_id>
  <start_dttm>@expression[request.user_def_dttm1]</start_dttm>
  <!-- This is where I'm having trouble -->
  <commit_dttm>@expression[request.user_def_dttm1 + Days(1)]</commit_dttm>
  <response_code>PRIMARY</response_code>
  <response_type>ARRIVAL</response_type>
  <commit_units>DAYS</commit_units>
  <commit_interval>@expression[1]</commit_interval>
  <calendar_id>24BY7</calendar_id>
  <status>ACTIVE</status>
  <description>Task SLA based on Planned Start Date - BR</description>
  <insert is_initialized="False" />
 </time_commit>
</update_time_commit>

 

This XML always adds Days(1) to the DTTM value, however, this doesn’t give me End of Day, for example, 16/07/2021 10:00AM + Days(1) will result in 17/07/2021 10:00AM and not 17/07/2021 12:00AM. I can’t use a hardcoded add Hours(X) because the DTTM could have different hours, for example 9:00AM, 10:00AM, etc, so the X value won’t always be the same.

 

I know that Today() always returns the current day at 12AM but I can’t use this since the DTTM also can be in the future.

 

Does anyone know an alternative on how to achieve this within an XML Business Rule?

 

Thanks in advance.

Regards,

João Moura

icon

Best answer by Saranga Amaraweera 16 July 2021, 19:44

View original
This topic has been closed for comments

2 replies

Saranga Amaraweera
Rank
Userlevel 7
Badge +22

Hi @JMoura,

 

Not entirely sure whether you would be able to set the value for the user_def_dttm1 to next day’s 12:00 AM via an expression as we have a limited number of expression functions for date formats. But you can definitely achieve this result from standard sql functions.

  1. Create a  custom sql view to get the dttm value from request table as per the expected output format. Note that DATEDIFF() function is capable of achieving your requirement. e.g.: CREATE VIEW AS yourviewname SELECT request_id, DATEADD(d,1,DATEDIFF(d,0,user_def_dttm1)) as output_date FROM request  
  2. Then once the view has been created and executed in fsm, you need to define that same view in custom metadata (make sure to retrieve the request id, output date with expected formatting ). 
  3. Modify your xml BR by wrapping the xml message inside perform_batch tags. Add a tabular or hierarchy select query to select the output_date filtered by the request_id first and then put the update xml message to update time_commit table with output_date via the xpath. 

            <perform_batch> 

            <!-- 1. add your tabular/hierarchy query to select the output_date from your custom view -- >

             <!-- 2. update time_commit table with your the result via xpath -- >

             </perform_batch>

SAAALK
J
Rank
Userlevel 2
Badge +5

Hi @Saranga Amaraweera,

 

I was just checking if it was possible to do this with some expression that I didn’t know of. Since it is not possible, I’ve gone with your suggested approach of creating a view and using it in the existing XML Business Rule and it worked as expected.

 

Thank you for your time and help!

 

Regards,

João Moura

Terms & ConditionsCookie settings