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’
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.
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.
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.
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 ?
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.
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.
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.