Date and time fields

Date and time fields API documentation - describes field formats, conversion examples, and usage in GraphQL queries and mutations.

Basic types

The Business NXT data model has featured fields that represent either dates or time values. These are stored in the database as integer values with some specific formats, as described in the following table:

TypeFormatExamples
dateyyyymmdd20190101 for 1 January 2019
timehhmm0 for 00:00, 101 for 01:01, 2330 for 23:30
precision time(((h * 60 + m) * 60) + s) * 1000 + ms0 for 00:00:00.000, 3661001 for 01:01:01.001, 2862000500 for 23:30:30.500

Since October 2024, all tables contain two new timestamp fields for created and changed date-time values. These are T-SQL datetime2 values (which in GraphQL are represented as DateTime values).

TypeFormatExamples
timestampyyyy-MM-dd HH:mm:ss[.nnnnnnn]2024-10-01 12:45:33.798 for 1 October 2024, 12:45:33.798
Tip

These fields, createdTimestamp and changedTimestamp, should be preferred for use in filters due to their higher precision.

However, these fields are not available for already existing rows, only for new or modified rows after the release date.

Tip

The date/time values represent the local time of the server. They do not indicate the timezone.

Since the GraphQL API exposes the data model as it is internally defined, the date and time fields appear in the API as integers. Here is an example:

Query
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    order(
      first : 2,
      filter : {orderDate : {_gt : 20150101}}
    )
    {

      items
      {
        orderNo
        orderDate
        createdDate
        createdTime
        changedDate
        changedTime
      }
    }
  }
}
Result
{
  "data": {
    "useCompany": {
      "order": {
        "items": [
          {
            "orderNo": 75,
            "orderDate": 20150115,
            "createdDate": 20200511,
            "createdTime": 1325,
            "changedDate": 20200511,
            "changedTime": 1325,
            "changeTimeInMs": 48308716
          },
          {
            "orderNo": 76,
            "orderDate": 20150115,
            "createdDate": 20200511,
            "createdTime": 1325,
            "changedDate": 20200511,
            "changedTime": 1325,
            "changeTimeInMs": 48308716
          }
        ]
      }
    }
  }
}

The use of the date and time fields require conversions as follow:

For date:

From integer
value = 20210122
year = value / 10000
value = value % 10000
month = value / 100
day = value % 100
To integer
value = year \* 10000 + month \* 100 + day

For time:

From integer
value = 1245
hour = value / 100
minute = value % 100
To integer
value = hour \* 100 + minute

For precision time:

From integer
value = 2862000500
ms = value % 1000
timeValue = value / 1000
hour = timeValue / 3600
timeValue = timeValue % 3600
minute = timeValue / 60
second = timeValue % 60
To integer
value = (((hour \* 60 + minute) \* 60) + second) \* 1000 + ms

The changeTimeInMs field is the only precision time field. However, this is only available on a limited number of tables, listed below:

  • Associate
  • AssociateReference
  • AssociateInformation
  • Appointment
  • Resource
  • Product
  • Barcode
  • PriceAndDiscountMatrix
  • Order
  • OrderLine
  • IncomingDocumentChange

On the other hand, the new timestamp fields apear as fields of the DateTime type, as shown in the following example:

Query
query read_orders($cid : Int!)
{
  useCompany(no: $cid)
  {
    order(
      first : 2,
      orderBy : {changedTimestamp : DESC})
    {
      items
      {
        orderNo
        orderType
        customerNo

        changedDate
        changedTime
        changedDateTime
        changedTimestamp

        createdDate
        createdTime
        createdDateTime
        createdTimestamp
      }
    }
  }
}
Result
{
  "data": {
    "useCompany": {
      "order": {
        "items": [
          {
            "orderNo": 4656,
            "orderType": 6,
            "customerNo": 10002,
            "changedDate": 20241010,
            "changedTime": 1022,
            "changedDateTime": "2024-10-10T10:22:30.399",
            "changedTimestamp": "2024-10-10T10:22:30.399",
            "createdDate": 20241010,
            "createdTime": 1022,
            "createdDateTime": "2024-10-10T10:22:00",
            "createdTimestamp": "2024-10-10T10:22:29.958"
          },
          {
            "orderNo": 1,
            "orderType": 2,
            "customerNo": 10002,
            "changedDate": 20240207,
            "changedTime": 813,
            "changedDateTime": "2024-02-07T08:13:14.764",
            "changedTimestamp": null,
            "createdDate": 20240217,
            "createdTime": 1325,
            "createdDateTime": "2024-02-17T13:25:00",
            "createdTimestamp": null
          }
        ]
      }
    }
  }
}

Date fields and time fields

In order to make it easier to work with date and time values, GraphQL is exposing all these fields also in an ISO date format and, respectively, a time format, according to the following table:

TypeFormatExamples
dateyyyy-mm-dd2019-01-01, 2021-12-31
timehh:mm00:00, 01:01, 23:30
precision timehh:mm:ss.ms00:00:00.000, 01:01:01.001, 23:30:30.500

To make this possible, every date or time field has a companion with the same name, but the suffix AsDate for dates and, respectively, AsTime for time. This is exemplified below:

FieldTypeValue
orderDateinteger20211022
orderDateAsDatedate2021-10-22
estimatedTimeinteger1710
estimatedTimeAsTimestring"17:10"
changeTimeInMsinteger48308716
changeTimeInMsAsTimestring"13:25:08.716"

Note: There is no natural date type to represent a time value without a date. Therefore, the type of the time fields with the AsTime suffix is actually string.

These date and time fields are available for:

Several examples are provided below.

Example: reading data from the system.

Query
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    generalLedgerAccount(first: 2)
    {
      items {
        accountNo
        name

        changedDate
        changedDateAsDate
        changedTime
        changedTimeAsTime
      }
    }
  }
}
Result
{
  "data": {
    "useCompany": {
      "generalLedgerAccount": {
        "items": [
          {
            "accountNo": 1000,
            "name": "Forskning og utvikling",
            "changedDate": 20210218,
            "changedDateAsDate": "2021-02-18",
            "changedTime": 1710,
            "changedTimeAsTime": "17:10"
          },
          {
            "accountNo": 1020,
            "name": "Konsesjoner",
            "changedDate": 20210219,
            "changedDateAsDate": "2021-02-19",
            "changedTime": 1020,
            "changedTimeAsTime": "10:20"
          }
        ]
      }
    }
  }
}

Example: filtering data.

Query
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    order(
      first : 2,
      filter : {
        orderDateAsDate : {_gt : "2015-01-01"}
      }
    )
    {
      totalCount
      items {
        orderNo
        orderDate
        orderDateAsDate
      }
    }
  }
}
Result
{
  "data": {
    "useCompany": {
      "order": {
        "totalCount": 268,
        "items": [
          {
            "orderNo": 75,
            "orderDate": 20150115,
            "orderDateAsDate": "2015-01-15"
          },
          {
            "orderNo": 76,
            "orderDate": 20150116,
            "orderDateAsDate": "2015-01-16"
          }
        ]
      }
    }
  }
}

Example: input values in insert and update mutations.

Query
mutation create($cid : Int!)
{
  useCompany(no: $cid)
  {
    order_create(values :[{
      orderNo : 999,
      orderDateAsDate : "2021-10-25"
    }])
    {
      affectedRows
      items
      {
        orderNo
        orderDate
        orderDateAsDate
      }
    }
  }
}
Result
{
  "data": {
    "useCompany": {
      "order_create": {
        "affectedRows": 1,
        "items": [
          {
            "orderNo": 999,
            "orderDate": 20211025,
            "orderDateAsDate": "2021-10-25"
          }
        ]
      }
    }
  }
}

Example: computing aggregates minimum and maximum.

Query
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    order_aggregate
    {
      minimum
      {
        orderDate
        orderDateAsDate
        changedTime
        changedTimeAsTime
      }

      maximum
      {
        orderDate
        orderDateAsDate
        changedTime
        changedTimeAsTime
      }
    }
  }
}
Result
{
  "data": {
    "useCompany": {
      "order_aggregate": {
        "minimum": {
          "orderDate": 20130105,
          "orderDateAsDate": "2013-01-05",
          "changedTime": 901,
          "changedTimeAsTime": "09:01"
        },
        "maximum": {
          "orderDate": 20151205,
          "orderDateAsDate": "2015-12-05",
          "changedTime": 1945,
          "changedTimeAsTime": "15:45"
        }
      }
    }
  }
}

Datetime special fields

Every Business NXT table contains the following fields:

FieldTypeDescription
createdTimestampdatetimeThe time point when the record was created (e.g. 2021-10-12 13:24:54.165).
createdDateintegerThe date when the record was created (e.g. 20211012).
createdTimeintegerThe time when the record was created (e.g. 1324).
createdUserintegerThe ID of the user that created the record.
changedTimestampdatetimeThe time point when the record was changed (e.g. 2021-10-12 09:02:13.843).
changedDateintegerThe date when the record was last changed (e.g. 20211023).
changedTimeintegerThe time when the recod was last changed (e.g. 902).
changedUserintegerThe ID of the user that made the last change to the record.
Tip

For precise values, prefer to use the createdTimestamp and changedTimestamp fields. These fields are available for all tables and have a higher precision than the date and time fields.

If these fields are null then you need to use createdDate/createdTime and changedDate/changedTime, respectively.

Warning

The created/changed date, time, and timestamp fields are only set when the records are physically preserved to the database. The backend performs the requested operations in memory and preserves results to the database at the end of the request. When you perform an insert and read back values for the created records, the read operation is performed while the records are still in memory. Therefore, if you retrieve after an insert the created date/time/timestamp fields, their value will be null or 0. A separate read operation (decoupled from the insert) will return the actual values.

The pair createdDate/createdTime represents the point in time when the record was created. Similarly, the pair changedDate/changedTime represent the point in time when the record was last changed. As previously mentioned, some tables have another field called changeTimeInMs that includes seconds and miliseconds to the time. These are important in different contexts, such as fetching data created or changed after a particular moment in time.

In order to simplify the use of these date-time values, the API makes these two pairs available through a compound field, as described in the following table:

FieldTypeExamples
createdDateTimedatetime2021-10-21T13:20:00
changedDateTimedatetime2021-10-22T14:59:00 or 2021-10-22T14:59:22.456 (where changeTimeInMs is available)

These values do not indicate the timezone (as previously mentioned). They represent the local time of the server. A companion set of fields suffixed with Utc are available, which represent the same date-time values, but in the Coordinated Universal Time (UTC) timezone.

FieldTypeExamples
createdDateTimeUtcdatetime2021-10-21T13:20:00Z
changedDateTimeUtcdatetime2021-10-22T14:59:00Z or 2021-10-22T14:59:22.456Z (where changeTimeInMs is available)

In the following example, the changedDateTime is used to select all the general ledger accounts that have been modified since 2021-10-01 17:00:00.

Query
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    generalLedgerAccount(filter : {
      changedDateTime : {
        _gte: "2021-10-01T17:00:00"}
    })
    {
      totalCount
      items
      {
        accountNo
        name
        createdDateTime
        createdDateTimeUtc
        changedDateTime
        changedDateTimeUtc
      }
    }
  }
}
Result
{
  "data": {
    "useCompany": {
      "generalLedgerAccount": {
        "totalCount": 2,
        "items": [
          {
            "accountNo": 9001,
            "name": "Special ACC",
            "createdDateTime": "2021-10-21T13:20:00",
            "createdDateTimeUtc": "2021-10-21T13:20:00Z",
            "changedDateTime": "2021-10-21T13:21:00",
            "changedDateTimeUtc": "2021-10-21T13:21:00Z"
          },
          {
            "accountNo": 9002,
            "name": "Extra ACC",
            "createdDateTime": "2021-10-21T13:20:00",
            "createdDateTimeUtc": "2021-10-21T13:20:00Z",
            "changedDateTime": "2021-10-21T13:21:00",
            "changedDateTimeUtc": "2021-10-21T13:21:00Z"
          }
        ]
      }
    }
  }
}

The use of the changedDateTime or createdDateTime field is equivalent to the use of an expression built with the changedDate/changedTime or changedDate/changeTimeInMs fields (when available) or, respectively, the createdDate/createdTime fields. Alternatively, the changedDateAsDate/changedTimeAsTime and the createdDateAsDate/createdTimeAsTime fields can be used.

Expression:

changedDateTime OP 2021-10-21T13:20:00

Equivalent to:

(changedDate OP 20211021) OR (changedDate == 20211021 AND changedTime OP 1320)

or

(changedDateAsDate OP "2021-10-21") OR (changedDateAsDate == "2021-10-21" AND changedTimeAsTime OP "13:20")

Expression (where changeTimeInMs exists):

changedDateTime OP 2021-10-21T13:20:10.500

Equivalent to:

(changedDate OP 20211021) OR (changedDate == 20211021 AND changeTimeInMs OP 48010500)

or

(changedDateAsDate OP "2021-10-21") OR (changedDateAsDate == "2021-10-21" AND changedTimeInMsAsTime OP "13:20:10.500")

Where OP is _gt, _gte, _lt, lte.

An example for the filter expression changedDateTime >= "2021-10-01T07:00:00" is provided below:

Query
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    generalLedgerAccount(filter : {
      changedDateTime : {
        _gte : "2021-10-01T07:00:00"},
    })
    {
      totalCount
      items
      {
        accountNo
        name
        createdDateTime
        changedDateTime
      }
    }
  }
}
Equivalent
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    generalLedgerAccount(filter : {
      _or : [
        {changedDate : {_gt : 20211001}},
        {
          _and : [
            {changedDate : {_eq : 20211001}},
            {changedTime : {_gte : 700}}
          ]
        }
      ]
    })
    {
      totalCount
      items
      {
        accountNo
        name
        createdDateTime
        changedDateTime
      }
    }
  }
}

To filter with a date time value that includes second and milliseconds the query and its equivalent are as follows (example for changedDateTime >= "2023-05-16T07:17:28.659"):

Query
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    order(
      filter : {
        changedDateTime : {
          _gte : "2023-05-16T07:17:28.659"}
      }
    )
    {
      totalCount
      items
      {
        orderNo
        orderDate
        changedDateTime
      }
    }
  }
}
Equivalent
query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    order(filter : {
      _or : [
        {changedDate : {_gt : 20230516}},
        {
          _and : [
            {changedDate : {_eq : 20230516}},
            {changeTimeInMs : {_gte : 26248659}}
          ]
        }
      ]
    })
    {
      totalCount
      items
      {
        orderNo
        orderDate
        changedDateTime
      }
    }
  }
}

The datetime format has the ISO8601 compliant form yyyy-mm-ddTHH:mm:ss.ms, such as 2021-10-01T07:00:00 and 2023-05-16T07:17:28.659 in the examples above.

In the case of filter objects, the changedDateTime and createdDateTime fields have a companion field called changedDateTimeTZ and createdDateTimeTZ, respectively. The values for thse two fields is expected to be in a specific timezone. Therefore, it will be converted to the local time before being compared with date and time values in the database.

query read($cid : Int!)
{
  useCompany(no: $cid)
  {
    order(
      filter : {
        changedDateTimeTZ : {
          _gte : "2023-05-16T09:17:28.659+02:00"}
      }
    )
    {
      totalCount
      items
      {
        orderNo
        orderDate
        changedDateTimeUtc
      }
    }
  }
}

The Business NXT time fields to not store seconds, only hours and minutes. For the tables that have the changeTimeInMs field available, seconds and milliseconds are also available. However, for the majority of tables, this field is not present. Regardless the case, when you use the createdDateTime and changedDateTime fields and specify a date-time value, you must also supply a value for seconds. Failure to do so, such as in the example 2021-10-01T07:00 will result in the following GraphQL error:

Equivalent
{
  "errors": [
    {
      "message": "Argument 'filter' has invalid value. In field 'changedDateTime': [In field '_gte': [Expected type 'DateTime', found \"2021-10-01T07:00\".]]",
      "locations": [
        {
          "line": 5,
          "column": 26
        }
      ],
      "extensions": {
        "code": "ARGUMENTS_OF_CORRECT_TYPE",
        "codes": [
          "ARGUMENTS_OF_CORRECT_TYPE"
        ],
        "number": "5.6.1"
      }
    }
  ]
}
Last modified September 18, 2025