--- title: "Date and time fields" description: "Date and time fields API documentation - describes field formats, conversion examples, and usage in GraphQL queries and mutations." lang: en languages: en: https://docs.vismasoftware.no/businessnxtapi/apireference/schema/datetime/index.md lastmod: 2026-07-10 --- > Documentation index: https://docs.vismasoftware.no/businessnxtapi/apireference/schema/llms.txt # Date and time fields Last modified July 10, 2026 > 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: | Type | Format | Examples | | ---- | ------ | -------- | | date | `yyyymmdd` | `20190101` for 1 January 2019 | | time | `hhmm` | `0` for `00:00`, `101` for `01:01`, `2330` for `23:30` | | precision time | `(((h * 60 + m) * 60) + s) * 1000 + ms` | `0` 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). | Type | Format | Examples | | ---- | ------ | -------- | | timestamp | `yyyy-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: ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order( first : 2, filter : {orderDate : {_gt : 20150101}} ) { items { orderNo orderDate createdDate createdTime changedDate changedTime } } } } ``` ```json { title = "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: ``` { title = "From integer" } value = 20210122 year = value / 10000 value = value % 10000 month = value / 100 day = value % 100 ``` ``` { title = "To integer" } value = year \* 10000 + month \* 100 + day ``` For time: ``` { title = "From integer" } value = 1245 hour = value / 100 minute = value % 100 ``` ``` { title = "To integer" } value = hour \* 100 + minute ``` For precision time: ``` { title = "From integer" } value = 2862000500 ms = value % 1000 timeValue = value / 1000 hour = timeValue / 3600 timeValue = timeValue % 3600 minute = timeValue / 60 second = timeValue % 60 ``` ``` { title = "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: ```graphql { title = "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 } } } } ``` ```json { title = "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: | Type | Format | Examples | | ---- | ------ | -------- | | date | `yyyy-mm-dd` | `2019-01-01`, `2021-12-31` | | time | `hh:mm` | `00:00`, `01:01`, `23:30` | | precision time | `hh:mm:ss.ms` | `00: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: | Field | Type | Value | | ----- | ---- | ----- | | `orderDate` | integer | `20211022` | | `orderDateAsDate` | date | `2021-10-22` | | `estimatedTime` | integer | `1710` | | `estimatedTimeAsTime` | string | `"17:10"` | | `changeTimeInMs` | integer | `48308716` | | `changeTimeInMsAsTime` | string | `"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: - [connection types](https://docs.vismasoftware.no/businessnxtapi/apireference/schema/queries/query/index.md) (used for reading data) - [aggregate types](https://docs.vismasoftware.no/businessnxtapi/apireference/schema/queries/aggregates/index.md) (only for the `minimum` and `maximum` aggregate functions) - [input types](https://docs.vismasoftware.no/businessnxtapi/apireference/schema/mutations/inserts/index.md) (used with mutations for inserting or updating records) - [filter types](https://docs.vismasoftware.no/businessnxtapi/apireference/features/filtering/index.md) (used for filtering records) Several examples are provided below. Example: reading data from the system. ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { generalLedgerAccount(first: 2) { items { accountNo name changedDate changedDateAsDate changedTime changedTimeAsTime } } } } ``` ```graphql { title = "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. ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order( first : 2, filter : { orderDateAsDate : {_gt : "2015-01-01"} } ) { totalCount items { orderNo orderDate orderDateAsDate } } } } ``` ```graphql { title = "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. ```graphql { title = "Query" } mutation create($cid : Int!) { useCompany(no: $cid) { order_create(values :[{ orderNo : 999, orderDateAsDate : "2021-10-25" }]) { affectedRows items { orderNo orderDate orderDateAsDate } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_create": { "affectedRows": 1, "items": [ { "orderNo": 999, "orderDate": 20211025, "orderDateAsDate": "2021-10-25" } ] } } } } ``` Example: computing aggregates `minimum` and `maximum`. ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order_aggregate { minimum { orderDate orderDateAsDate changedTime changedTimeAsTime } maximum { orderDate orderDateAsDate changedTime changedTimeAsTime } } } } ``` ```graphql { title = "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: | Field | Type | Description | | ----- | ---- | ----------- | | `createdTimestamp` | datetime | The time point when the record was created (e.g. `2021-10-12 13:24:54.165`). | | `createdDate` | integer | The date when the record was created (e.g. `20211012`). | | `createdTime` | integer | The time when the record was created (e.g. `1324`). | | `createdUser` | integer | The ID of the user that created the record. | | `changedTimestamp` | datetime | The time point when the record was changed (e.g. `2021-10-12 09:02:13.843`). | | `changedDate` | integer | The date when the record was last changed (e.g. `20211023`). | | `changedTime` | integer | The time when the recod was last changed (e.g. `902`). | | `changedUser` | integer | The 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: | Field | Type | Examples | | ----- | ---- | ----------- | | `createdDateTime` | datetime | `2021-10-21T13:20:00` | | `changedDateTime` | datetime | `2021-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. | Field | Type | Examples | | ----- | ---- | ----------- | | `createdDateTimeUtc` | datetime | `2021-10-21T13:20:00Z` | | `changedDateTimeUtc` | datetime | `2021-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`. ```graphql { title = "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 } } } } ``` ```graphql { title = "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: ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { generalLedgerAccount(filter : { changedDateTime : { _gte : "2021-10-01T07:00:00"}, }) { totalCount items { accountNo name createdDateTime changedDateTime } } } } ``` ```graphql { title = "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"`): ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order( filter : { changedDateTime : { _gte : "2023-05-16T07:17:28.659"} } ) { totalCount items { orderNo orderDate changedDateTime } } } } ``` ```graphql { title = "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: ```json { title = "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" } } ] } ``` --- [View this page](https://docs.vismasoftware.no/businessnxtapi/apireference/schema/datetime/)