Hi everyone,
I'm trying to upload my data table into SQL & I'm having issues getting the date columns to load in.
The error I receive is this: "[Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: numeric is incompatible with date"
I tried using SELECT CAST to make it into a date format but that didn't work. I'm using JMP 17.0.0.
I used the base code from this post & tried to adapt it to what I needed to no avail: https://community.jmp.com/t5/Discussions/How-to-Insert-Append-Write-data-to-SQL-Server-database-in-t...
dt = data table ("Untitled 2");
col_list = dt << get column names(string);
col_names = concat items(col_list, ", ");
insert_prefix = evalinsert(
"SELECT CAST(dt:Date as date)";
"SELECT CAST(dt:Accrual_Period_Date as date)";
"INSERT INTO [Sandbox].[dbo].[XXXXXXXX] (^col_names^) VALUES ");
dbc = create database connection("ODBC:Description=;DRIVER=SQL Server;SERVER=XXXXX;UID=XXXX.XXXX;PWD=XXXXX;APP=JMP;WSID=XXXXXX;DATABASE=Sandbox;");
for (i = 1, i <= nrows(dt), i++,
for (k = 1, k <= ncols(dt), k++,
one_col = col_list[k];
one_value = column(dt, one_col)[i];
// Surround strings with single quotes
if (type(one_value) == "String",
one_value = "'" || one_value || "'";
,
// Convert numeric to string, handle missing values (.)
one_value = char(one_value);
if (one_value == ".", one_value = "NULL");
);
if (k == 1,
sql_string = one_value;
,
sql_string = sql_string || ", " || one_value;
);
);
insert_sql = insert_prefix || "(" || sql_string || ")";
show(insert_sql);
// Do the insert. Execute SQL does an implicit commit
execute sql(dbc, insert_sql);
);
close database connection(dbc);
Any help would be appreciated!
