Date/time attributes

0
Is there a widget/microflow that will derive attributes about a date?  For example, if I have a record with a timestamp 2018/07/17 20:13:58, can I get other values like Month end date Week end date Year Quarter Hour
asked
2 answers
6

Hi Dave,

You can use the date functions to accomplish this. Some of these ways may be easier to accomplish using a java action though. 

Month end date - Doing this in a microflow can be a little tricky but you can use the addMonth function to add a month, then use the trimToMonth function to get the date to the first date of the month, and then you can use the addDays function to add a negative day to get the last day of the month.

first add a month

addMonths($date, 1) 

Then get the date to be the first of the month

trimToMonths($date)

Then subtract a day

addDays($date, -1)

I'm not too sure how it would work if you nested all these functions. You could play around with it.

Week end date - You could use the parseInteger function and the formateDateTime function to return the day of the week as an integer. Monday being 1, and Sunday being 7. Then you can subtract this integer by 7 and use the addDays function to add the difference to your date to get the week end date.

Get the day of the week as an integer and store it in a variable called $WeekDay

parseInteger(formatDateTime($date, 'u'))

Subtract the day of the week integer by 7 and store in a variable called $Difference

$WeekDay - 7

Add the difference to your date to get the end of the week date

addDays($date, $Difference)

Year - This would just use the formatDateTime function and regex

formatDateTime( $dateVariable, 'yyyy')

Quarter - I think for this one you want to see what quarter the date falls in? So if we started Jan 1st and ended Dec 31st, Jan - Mar would be quarter 1, Apr - June would be quarter 2, etc. For this you could define a start and end date for your period (maybe with constants or how ever you want to do it), then in a microflow you could use DaysBetween with your start and end date to return an integer, then you can divide that integer by 4, and then create 4 variables using the add days function to define your quarters. Then using exclusive splits you can do a check for each quarter to see if the date is less than or equal to each quarter.

Start with getting days between your period and store it in a variable called $NumberofDays

daysBetween($StartPeriod, $EndPeriod)

divide NumberOfDays by 4, and store that in a variable called $quarter

$NumberOfDays : 4

Then create 4 date time variables that would be your q1, q2, q3, q4.

q1

addDays($startPeriod, $quarter)

q2

addDays($q1, $quarter)

q3

addDays($q2, $quarter)

q4

addDays($q3, $quarter)

Then use 4 exclusive splits to check if your date is less than or equal to q1, q2, q3, or q4, and then you can determine which quarter that date falls under.

Hour - This would just use the formatDateTime function and regex

 formatDateTime( $dateVariable, 'HH')

 

Hope this helps!

answered
3

Dave,

You can get these using a variety of tools:

  • to get Month End Date, create a variable with the following formula: addDays(addMonths(trimToMonths($YourDate),1),-1)   Note that this will give you the beginning of the last day of the month your date falls in.  If you want the end of that day, you could use addMinutes(  *ExpressionsAbove*,-1) in place of addDays
  • Week end date, you can use parseInteger(FormatDateTime($YourDate,'u')).  This will give you the day of the week (1 = Monday, 7 = Sunday).  Then you can create a variable as above addDays($YourDate,(7-DayFromPriorFunction)
  • to get Year, use the Java action GetIntFromDateTime in Community Commons
  • to get Quarter, get Year as above, then get Month using the same function and create some microflow logic to determine what quarter the Month is in.
  • to get Hour, use FormatDateTime 

These documentation links may be useful:

https://docs.mendix.com/refguide/trim-to-date

https://docs.mendix.com/refguide/parse-and-format-date-function-calls

https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

https://docs.mendix.com/refguide/parse-integer

Hope that gets you started.

Mike

 

answered