Append JMP Data Table into SQL database with Dates

Bagawandoss96 — Wed, 20 Sep 2023 22:28:12 GMT

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-table/m-p/262281#M51331

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!

Re: Append JMP Data Table into SQL database with Dates

mmarchandTSI — Thu, 21 Sep 2023 12:00:52 GMT

I send date and datetime values as strings, like '1914-12-20'

topic Append JMP Data Table into SQL database with Dates in Discussions

Append JMP Data Table into SQL database with Dates

Re: Append JMP Data Table into SQL database with Dates