Hello - We have a powerApp that writes to a SQL Server DB. Most users are successful when writing to the DB. A handful are experiencing an issue where the date column(s) is putting in the year as 1477 instead of 2020. As you could imagine this is a problem. The column definitions are as follows. ForecastYear: year the forecast is for, ForecastMonth: Month the forecast is for, ReportingForecastDate: the first day of the month for the given years forecast, CurrentForecastDate: todays date, SixtyDayForecast: todays date (Null is also acceptable). ForecastYear and ForecastMonth are both good but ReportingForecastDate, CurrentForecastDate and SixtyDayForecast are wrong.
EDIT (5/27/20): I have found what I think to be causing the problem. I would enjoy hearing anyone suggestion on to fix it or if they ran into a similar issue.
I have narrowed it down to users who have their language set to "Thai". Thai uses a calendar that is 543 years behind the Gregorian calendar, thus the year 1477. Using the debugger (or whatever it is called) I see PowerApps creates a header "language" when sending to the SQL server API/Connector. My guess is this header is used in SQL server when creating the date, and this is what is causing my issue.
I could try and add 543 years onto each date if the PowerApps Language() function evaluates to "th". However, to me, this seems like a temporary fix. Another language could use a calendar different than Gregorian and I would need to implement custom code for every language that differs. Ideally I would want to override language or be able to exclude that header in the SQL Server API/Connector.
Thanks for any insight!
Would really appreciate any help/insight.
Code to collect the record:
Collect(toAdd, {SalespersonId:varCurrentUser.SalespersonId, CustomerId:ThisItem.CustomerId , CustomerShipToId:ThisItem.CustomerShipToId ,BUAreaCode:ThisItem.BUAreaCode , BUManagementGroupCode:ThisItem.BUManagementGroupCode, ItemId:ThisItem.ItemId, ForecastYear:Value(Text(DateAdd(Now(), 5, Months), "[$-en-US]yyyy")), ForecastMonth:Value(Text(DateAdd(Now(), 5, Months), "[$-en-US]m")), CurrentForecastQty:Value('txtFData-M5'.Text), SixtyDayForecastQty:Value('txtFData-M5'.Text), CurrentForecastDate:Today(), SixtyDayForecastDate:Date(Value(Text(DateAdd(Now(), 0, Months), "[$-en-US]yyyy")), Value(Text(DateAdd(Now(), 0, Months), "[$-en-US]m")), Value(Text(DateAdd(Now(), 0, Months), "[$-en-US]d"))), ReportingForecastDate:Date(Value(Text(DateAdd(Now(), 5, Months), "[$-en-US]yyyy")), Value(Text(DateAdd(Now(), 5, Months), "[$-en-US]m")), 1) })
)
How the SQL Server Record Looks:
