The date and time functions can be used to create a date/time from integers, perform calculations and get individual parts of a date/time. To convert from or to other data types have a look at Conversion functions. When working with date/time the rules that Blueriq uses to interpret and format are important to know:
Since Blueriq 17.0 all dates are consistently interpreted using the (proleptic) Gregorian calendar rules. Previously, in earlier versions all dates before 15 October 1582 where interpreted using the Julian calendar rules. This change ensures greater consistency in date calculations, addressing potential discrepancies that could arise when different calendar rules were applied to dates in the same calculation.
Time zone rules have evolved over time because different regions and countries have adjusted their policies to meet economic, political, and societal needs. For example, a country might shift its time zone to align with neighboring regions for trade or due to a political change. A historical example is the "Europe/Amsterdam" time zone, where the offset changed by 40 minutes in 1940 due to wartime decisions.
Blueriq uses the server’s default time zone (local time) for processing time and date data. When the system generates or converts dates and times, it consistently applies the time zone rules of the server's configured time zone.
However, Blueriq’s conversion functions do not currently support switching time zones or including time zone information in date and time formats or patterns. For example, when using the TEXT conversion function to create a date-time string like "2025-02-17 12:31:45," it will be based on the server’s default time zone, not any other specific time zone.
This means if you need to convert or format a date/time in a different time zone, Blueriq currently doesn’t offer built-in support to handle that.
Blueriq recognizes Daylight Savings Time (DST), but during calculations, days are always treated as 24 hours long, even on DST switch dates. This means that days are not considered to be 23 or 25 hours long when DST begins or ends.
Starting with Blueriq 17.0, times within a DST gap (such as 2006-03-26T02:00:00 Europe/Amsterdam) are no longer considered invalid. Instead, they are automatically adjusted to the time after the gap. In the example, this would mean the time shifts from 02:00:00 to 03:00:00.
Occasionally, leap seconds are added to account for irregularities in Earth's rotation. This means that a time like "23:59:60" may exist before transitioning to the next day. Currently, Blueriq does not support the direct construction of date/times with leap seconds.
The way in which date and time values are displayed in the user interface is determined by the date display formats, which can be configured per language code. |
| Function | Description |
|---|---|
DATETIME | Creates a dateTime value out of integers. |
DATE | Creates a date value out of integers. |
ADD PERIOD TO DATE | Calculates a new date by adding a specified period to a specified date. |
ADD PERIOD TO DATETIME | Calculates a new dateTime by adding a specified period to a specified dateTime. |
SUBTRACT PERIOD FROM DATE | Calculates a new date by subtracting a specified period from a specified date. |
SUBTRACT PERIOD FROM DATETIME | Calculates a new dateTime by subtracting a specified period from a specified dateTime. |
PERIOD BETWEEN | Calculates the period between two date or dateTime objects. |
| Calculates the working days between two date or dateTime objects. | |
WORKINGDAYS_BETWEEN | Alternative notation for WORKINGDAYS BETWEEN. |
| Returns the year of a date or dateTime value. | |
MONTH | Returns the month of a date or dateTime value. |
DAY | Returns the day of a date or dateTime value. |
HOUR | Returns the hour of a dateTime value. |
MINUTE | Returns the minute of a dateTime value. |
SECOND | Returns the second of a dateTime value. |
TODAY | Returns the current date of the server. |
NOW | Returns the current date and time of the server. |
WEEKNUMBER | Returns the week number within the year of a given date or dateTime value. |
DAYOFWEEK | Returns the number of the day within the week of a given date or dateTime value. |