Unable to insert/update dates into MSSQL tables

Help me!
1

Feature(s) impacted

Add New Record to MSSQL Table

Observed behavior

Attempting to insert a date as a string e.g. ‘2023-10-04 09:28:58.000 +00:00’ - throws an error.

Error is:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Expected behavior

Should be able to insert/update the row. Updating a date field (via an insert or update) causes issues. But should be able to update date field.

Failure Logs

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Original update statement

exec sp_executesql @statement=N’UPDATE [xxx].[xxxx] SET [createddate]=@0,[xxxx]=@1 WHERE [xxxx] = 1’,@params=N’@0 nvarchar(30), @1 nvarchar(108)‘,@0=N’2023-06-25 05:35:48.000 +00:00’,@1=N’World’‘s largest miner, offering iron ore, copper, coal, nickel, and petroleum products for global industries’

Context

  • Project name: Equities (POC)
  • Team name: Operations
  • Environment name: Prod
  • Agent (forest package) name & version: Latest
  • Database type: MSSQL
  • Recent changes made on your end if any: N/A
2

Hello @David_Martin :wave: ,

Can you tell me which version of mssql you are using :pray: ? It would help me to reproduce your issue :slight_smile:

Kind regards,

Florian

1 Like
3

Apologies —> 15.0.4236.7

4

Hi @David_Martin

I’m sorry I cannot reproduce, can you send me the payload of your browser when doing the creation request ?
I think the issue is due to the “+00:00” added at the end, it shouldn’t be there.

6

Apologies, forgot to add. I set the timezone in the project to Atlantic/St Helena to save GMT times. That is probably where the timezone value is coming from.

We need to store GMT data in our date fields, regardless of where the user is.

7

Ok, I found the issue, it seems like the text input widget does not allow to correctly save date fields, it’s an issue on our end, we’re looking into fixing it.
By then, the only way to edit date fields is the date picker widget.

For the timezone, what is send to the agent is a date converted with a +00:00 timezone. Your current timezone is then reapplied when the date is displayed in your frontend.
If you want to store a specific timezone in your record, you’ll have to add a new column that stores this information.

I’ll keep you updated about the fix of the text input.

1 Like
8

Thanks for letting me know. I did use the date picker widget and the date string it put into the box is from the calendar / date picker.

Thanks for following up /remediating so quickly.

How do I get the code update / fix?

Regards,
Dave

9

I’m not sure to understand your situation well. Where does the field is a String type ? What “box” are you talking about ?
If you used a Date picker widget, the type sent to the agent is a Date at UTC format, and should be parsed by your DB.

Could you send me the payload of the create/update query sent from your browser ?

Regards,
Enki

1 Like
10

Will do today apologies for the delay.

11

Hello @David_Martin,

Any news on your issue ?

1 Like
12

Need to access to create a new SQL Instance, you guys turned off the feature :frowning: . Cant test at the second.

13

Hey @David_Martin,

You can refresh your browser. The MSSQL onboarding should be back.
You could also have used the Advanced Setup > Microservice > MicrosoftSQL that create a standalone Node.js app that you can run locally or in a Docker. :smiling_face:

1 Like
14

Do you have a private email address I can share the pay load via. Dont want to make it public :slight_smile:

15

Just sent a DM :slight_smile: - let me know if you need anything else

16

I’ll try to sum up your issue:

  • You have a Date field, and using the datepicker to edit this field in Forest Admin.
  • Your frontend correctly sends the date at ISO 8601 format, UTC time (timezone GMT+0): "CreatedDate":"2023-10-11T06:20:33.000Z".
  • Forest Admin agent should receive this date and insert it in your DB, but somehow it is converted to an other format (2023-10-04 09:28:58.000 +00:00), also following the ISO norm but that your DB cannot parse.

You indicated that you “set the timezone in the project to Atlantic/St Helena to save GMT times”, where did you set the timezone ? I believe that there is some date computation between your server/agent and your DB.

17

In this example (new project) I have made not time zone changes. This is “out of the box”

18

And you still have the same error from your DB ?
What is the type of the column in your DB ? datetime or datetime2 ?
I think you should use datetime2, as recommended by MSSQL documentation, to be able to parse the timezone.

19

Alternativeley you can ensure the date sent to your DB is in the right format using collection.replaceFieldWriting in your customizations :

collection.replaceFieldWriting('endDate', (value: string) => {
  const updatedValue = new Date(Date.parse(value)).toISOString();

  return { endDate: updatedValue };
});
20

I use datetime - I will try datetime2 and let you know.

21

Hello @David_Martin

Did you solved your issue ?