Error writing to SQL Server table

12 次查看（过去 30 天）
Manny
Manny 2024-12-9，20:45
回答： Walter Roberson 2024-12-9，22:08
Hi everyone
I am getting an error when I try to write MATLAB data to SQL Sever table.
Code:
ForecastedData = ['SPX' datetime("today") 1 2 3 4 5 6 7 8 9 10];
% WRITE RESULTS TO SQL SERVER TABLE
sqlwrite(ConnectionString, CIVForecast, ForecastedData);
Error:
Error using datetime/horzcat (line 715)
Unable to convert the text 'SPX' to a datetime value because its format was not recognized.
Description:
The SQL Server table has 12 columns with different data types:
[SYMBOL] [nvarchar](10) NOT NULL,
[FORECAST_DATE] [datetime] NOT NULL,
[DAY01] [numeric](6, 2) NOT NULL,
[DAY02] [numeric](6, 2) NOT NULL,
[DAY03] [numeric](6, 2) NOT NULL,
[DAY04] [numeric](6, 2) NOT NULL,
[DAY05] [numeric](6, 2) NOT NULL,
[DAY06] [numeric](6, 2) NOT NULL,
[DAY07] [numeric](6, 2) NOT NULL,
[DAY08] [numeric](6, 2) NOT NULL,
[DAY09] [numeric](6, 2) NOT NULL,
[DAY10] [numeric](6, 2) NOT NULL
The first value is supposed to be a text but MATLAB keeps trying to convert it to datetime which is wrong. I need to construct ForecastedData so it has the same datatypes as SQL Server table. If not, then the import will fail because SQL Server won't allow the wrong data type to be imported.
How can I fix ForecastedData?
Thank you

请先登录，再回答此问题。

回答（1 个）

Walter Roberson
Walter Roberson 2024-12-9，22:08
Ran in:
ForecastedData = ['SPX' datetime("today") 1 2 3 4 5 6 7 8 9 10]
Error using datetime/horzcat (line 715)
Unable to convert the text 'SPX' to a datetime value because its format was not recognized.
The [] operation is "syntactic sugar" for horzcat or vertcat calls. There is automatic conversion of datatypes so that the end result of the [] operation is an array that is all the same datatype.
Your second element is datetime("today") which results in a datetime object.
There is no defined way to convert arbitrary character vectors such as 'SPX' to datetime objects. If the 'SPX' just happened to be a character vector representing a date and time then it would have been converted. For example,
['01-jan-1970' datetime("today")]
would result in a vector of two datetime objects.
The data accepted for sqlwrite is strictly table() datatype, not a vector of values.
varnames = ["SYMBOL", "FORECAST_DATE", compose("DAY%02d", 1:10)];
data = cell2table({'SPX' datetime("today") 1 2 3 4 5 6 7 8 9 10}, 'VariableNames', varnames);
sqlwrite(ConnectionString, CIVForecast, ForecastedData);

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by