Skip to main content
Solved

Expression to convert Datetime to End of Day

J
Do Gooder (Partner)
Forum|alt.badge.img+5
  • JMoura
  • Do Gooder (Partner)
  • 7 replies

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

Best answer by Saranga Amaraweera

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>

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

2 replies

Saranga Amaraweera
Superhero
Forum|alt.badge.img+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
1 person likes this
  • J
    JMoura
J
Do Gooder (Partner)
Forum|alt.badge.img+5
  • JMoura
  • Author
  • Do Gooder (Partner)
  • 7 replies
  • July 20, 2021

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

Saranga Amaraweera
1 person likes this
  • Saranga Amaraweera
    Saranga Amaraweera
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings