REST date/datetime bug reality check

Kinetic ERP
1

If someone has a few minutes can I beg a favor? We’ve got a new bug we’re working on with support and in the interest of helping them help me I’m curious if it’s just us or not.

When you pass a date through a BAQ, a date value 2023-01-01 returns as “1/1/2023”. I’d attach a BAQ as a means to replicate but it would take longer to import than to write from scratch:

select 
	(cast('20230101' as date)) as [Calculated_foo]

Until recently, when I retrieved that via REST I’d get 2023-01-01T00:00:00-06:00 which is technically wrong but at least enough of it was right to deal with.

However! Now when I run that over REST (V2) I get 2022-12-31T18:00:00-06:00 which has bumped my distributed reporting all off kilter.

2

@kananga I am getting the 0s in 11.1.200. What version are you running?

“2023-01-01T00:00:00-05:00”

3

Given you are UTC-6 and it is showing 1800 or 6PM I would guess it has to do with when you are running the report. I have a 11PM report that does not run on Friday unless I check Saturday as enabled

4

Total SWAG here, but do you have a time zone set in your site? If so, are you in that Site when making the REST call? Also, you can have a time zone on System Agents IIRC.

1 Like
5 
select 
	(cast('20230101' as date)) as [Calculated_foo1],
	(cast('20230101' as datetime)) as [Calculated_foo2]

{
  "@odata.context": "xxxx/BaqSvc/kananga/$metadata#Data",
  "value": [
    {
      "Calculated_foo1": "2023-01-01T00:00:00Z",
      "Calculated_foo2": "2023-01-01T00:00:00Z",
      "RowIdent": "00000001-0000-0000-0000-000000000000"
    }
  ]
}

SaaS 2023.1.5

6

Hmm Yours are coming back Zulu

7

See if CONVERT gives you the same results.

select 
	(cast('20230101' as date)) as [Calculated_foo1],
	(cast('20230101' as datetime)) as [Calculated_foo2],
	(convert (date, '20230101')) as [Calculated_foo3],
	(convert (date, '20230101', 127)) as [Calculated_foo4],
	(SYSUTCDATETIME()) as [Calculated_sysutcdt],
	(Convert (datetime, SYSDATETIMEOFFSET())) as [Calculated_sysdto]```
```js
{
  "@odata.context": "xxxxxx/BaqSvc/kananga/$metadata#Data",
  "value": [
    {
      "Calculated_foo1": "2023-01-01T00:00:00Z",
      "Calculated_foo2": "2023-01-01T00:00:00Z",
      "Calculated_foo3": "2023-01-01T00:00:00Z",
      "Calculated_foo4": "2023-01-01T00:00:00Z",
      "Calculated_sysutcdt": "2023-06-22T00:36:11.8514113Z",
      "Calculated_sysdto": "2023-06-21T19:36:11.85Z",
      "RowIdent": "00000001-0000-0000-0000-000000000000"
    }
  ]
}
8

Whatever the issue is, it’s going to be on your sql server, not the app server.

Cast/convert is done in sql directly.

9

2023.1.5 for the win

{
  "@odata.context": "https://stp2edv03/Kinetic/api/v2/odata/COMP01/BaqSvc/test/$metadata#Data",
  "value": [
    {
      "Calculated_foo": "2022-12-31T19:00:00-05:00",
      "Company_Company": "COMP01",
      "Company_Name": "Dev Database 04/28/2023",
      "Calculated_foo_convert": "2022-12-31T19:00:00-05:00",
      "Calculated_foo_dt": "2022-12-31T19:00:00-05:00",
      "RowIdent": "00000001-0000-0000-0000-000000000000"
    }
  ]
}

image

10

Still incorrect. Kinetic just knew about the offset.

11

correct in baq, but wrong just like his in rest.

12

I’m off the pc, could you convert one back to string from a cast all in one calc field please :slight_smile:

13

All good in the baq and rest

  "Calculated_foo_convert": "2023-01-01",
14

Yep, also we’re SaaS. Pacific time on the ground, Central on the server.

The fun part is our server is UTC-5 right now, UTC-6 on 2023-01-01! If I pass today’s date I get UTC-5. So it’s related to the value not any timezone settings.

15

That’s really interesting! It looks like there’s a configuration somewhere that changes how date/time is handled over REST. I’ve never seen straight UTC time format on our SaaS instance. Even the SQL datetimeoffset type is converted. I’ve only ever seen a datetimeoffset, except V1 REST, that still returns an unqualified datetime (YYYY-MM-DDThh:mm:ss).

It’s definitely something going on post-SQL. Dates and datetimes are coming out of queries fine, no problem running in BAQ or dashboards or SSRS.

16

That confirms the sql server is good, and Epicor is screwing with the time.

I stand corrected.

17

Thanks to all! I want to mark everything as a solution.

I’ll certainly pass on to support that this is impacting at least one other SaaS instance. Anybody else? I’ll also post my support ticket# for others to reference when I’m back at my work computer tomorrow morning.

2 Likes
18

@kananga to be clear my 23.1.5 is an on prem instance I am playing with.

19

That’s the way it is sometimes :rofl: :dumpster_fire:

I wouldn’t call this solved though, please keep us posted.

20

@kananga do you have a case number that I can reference? I submitted a support case this morning for this same issue before I realized that others have already discovered this.