Datacalculation

From SMTX Wiki
Jump to navigation Jump to search

Show a date from a datefield in format dd/MM/yyyy:

  format('{0:dd/MM/yyyy}',TODATE('#REPLACE-FORMFIELD-SRE_Visitor_CheckIn-3||Meeting_Start_Date_Time||#'))


To calculate the difference between 2 dates : make a readonly display field, with this calculation :

 DATEDIFF(todate('#REPLACE-FORMFIELD-DateChecks||Date1||#'),todate('#REPLACE-FORMFIELD-DateChecks||Date2||#'), 'H') 

Note: the 'H' value stands for Hours. You can use:

  • D : Days
  • DDD : Short name of the day (Mo, Tu, We)
  • DDDD : Full name of the day (Monday, Tuesday, Wednesday)
  • MMM: Short name of the month
  • MMMM: Full name of the month
  • H : Hours
  • M : Minutes
  • S : Seconds

sample form : click here


Convert the date to the last date of the current month

NCALC expression example:

DATEADD(DATEADD(TODATE(FORMAT('{0:yyyyMM}01', NOW()), 'yyyyMMdd'),1,'M'),-1,'d')

Result is a date, if you want to convert this to a string, you need to use the Format function and provide a date structure: FORMAT('{0:dd/MM/yyyy}',DATEADD(DATEADD(TODATE(FORMAT('{0:yyyyMM}01', NOW()), 'yyyyMMdd'),1,'M'),-1,'d'))

Explanation: - Take today (NOW()) - Use the first day of the current month (for example, convert to text and set day to 01: yyyyMM01, this string can be converted back to date format) - Add 1 month and substract 1 day

sample form: click here

Extra function to add units to a date:

Example

FORMAT('{0:yyyyMMdd}',DATEADD(TODATE('#REPLACE-FORMFIELD-form1||FieldDate||#'),6,'M'))


Arg1: date field to which you want to add something. Note that this argument needs to be in date format. Use todate when using strings. In case the value is not coming from a date field, also provide the date format.

Arg2: Number of units to add (must be an ncalc NUMBER). If you get the value from another field, first convert to number using ToNumber()

Arg3: Units: Y,M,D,h,m,s (not the capital M vs small m, for Month, resp. minute)

The result of DateAdd is a ncalc DATE variabele, and can't be used directly as value for a field. If you want to use it in a date time field, you first need to convert the format to yyyyMMdd (for date) or yyyyMMddHHmmss (for datetime). Convert to a string as in the example before.