Error writing to SQL Server table

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

[Manny]

issue resolved. my answer is here:

https://www.mathworks.com/help/database/ug/database.odbc.connection.sqlwrite.html

Answer 1

[Walter Roberson]

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);