Date and Time Variables

If you need to change the SQL query dynamically, you can use predefined variables. BellaDati currently supports functions to get date, time or timestamp in user defined formats:

Name

Description

Examples

$date(dateString)

Evaluates the dateString and outputs the date in yyyy-MM-dd format. The dateString

$date(now + 5d -4w)
$date(2011-01-01 + 5d -4w)
$date(actualMonth -1d)

$date(dateString, format)

Works like $date(dateString), but output format is controlled by format parameter

$date(now + 5d -4w, dd-MM-yyyy)
$date(2011-01-01 + 5d -4w, MMyyyy)
$date(actualMonth -1d, yyyy-dd-MM)

$time(timeString)

Evaluates the timeString and outputs the resulting time in HH:mm:ss format

$time(now)
$time(actualhour)
$time(actualminute)

$time(timeString, format)

Works like $time(timeString), but output format is controlled by format parameter

$time(now, HH:mm:ss)
$time(actualhour, MMss)
$time(actualminute, HHmmss)

$timestamp()

Returns the current time stamp value

$timestamp()
$lastSuccessfulImport()
Returns time stamp with last successful import
$lastSuccessfulImport()

$firstValue(L_ATTRIBUTE_CODE)

Returns the lowest value (sorted ascending) of the attribute specified by attribute ID stored in the current data set.

Returns empty string if there are no data or the attribute code is not valid.

$firstValue(L_ID) //returns 123456

$firstValue(L_DATE_ATTRIBUTE) //returns 2013-01-01
$firstValue(L_TIME_ATTRIBUTE) //returns 10:00:54

$lastValue(L_ATTRIBUTE_CODE)

Returns the highest value (sorted descending) of the attribute specified by attribute ID stored in the current data set.

Returns empty string if there are no data or the attribute code is not valid.

$lastValue(L_ID) //returns 123456

$lastValue(L_DATE_ATTRIBUTE) //returns 2013-12-31
$lastValue(L_TIME_ATTRIBUTE) //returns 23:59:59
$firstValue(DATASET_CODE, L_ATTRIBUTE_CODE)Returns the lowest value (sorted ascending) of the attribute specified by attribute ID stored in the referenced data set.$firstValue(NEW_DATA_SET__SQL_DATABAS_4, L_ATT)
$lastValue(DATASET_CODE, L_ATTRIBUTE_CODE)Returns the highest value (sorted descending) of the attribute specified by attribute ID stored in the referenced data set.$lastValue(NEW_DATA_SET__SQL_DATABAS_4, L_ATT)

When using the variables in the SQL query, you should make sure that the variable is placed in the quotes, so it will be used as string.

 

An example of such a condition can be: WHERE id > '$lastValue(L_ID)'

DateString

  • now - represents actual date
  • actualyear - represents the first day of actual year (1.1.20XX). For example actualyear selected on 21.9.2010 represents date 1.1.2010
  • actualquarter - represents the first day of actual quarter (1.1.20XX, 1.4.20XX, 1.7.20XX, 1.10.20XX). For example actualquarter selected on 21.9.2010 represents date 1.7.2010
  • actualmonth - represents the first day of actual month (1.1.20XX, 1.2.20XX, ...). For example actaulmonth selected in 21.9.2010 represents date 1.9.2010
  • actualweek - represents first day of actual week (Monday). For example actualweek selected on 21.9.2010 represents date 20.9.2010 (Monday of this week in calendar)
  • availableFrom, availableTo - represents the first and last available date entry
  • relative and absolute enterig of date can be adjusted by operators using this syntax: date +|- n[d|w|m|q|y], where n is integer, d represents day, w represents week, m represents month q represents quartal and y represents year. We can for example define time in this way:  actualyear + 2m -4d. Today is 21.9.2010, so this value represents 1.1.2010 + 2 months - 4 days, which means date 25.2.2010.

TimeString

  • now - represents actual time
  • actualhour - represents the actual hour at 0 minutes and 0 seconds.
  • actualminute - represents the actual minute at 0 seconds
  • actualsecond - represents the actual second