Error When Trying to Load 'DATETIME' Columns from SAS to Snowflake

[u787bruw]

Hello, I am trying to BULKLOAD a SAS dataset into Snowflake and I am having trouble with the DATETIME column in my SAS dataset. When trying to load it into Snowflake, I get the error message:

ERROR: Error executing COPY command: Numeric value '2016-01-08 14:20:34.000000000' is not recognized
  File 
       '@~/nucleus/xxxxx.gz', line 6187, character 186
  Row 6187, column 
       "CLR_CALL_ACTIV_HIST_2016"["INTERACTION-ID":21]
If you would like to continue loading when an error is encountered, use 
other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 
'info loading_data' in a SQL client.

For reference, here is my SAS code:

/* Transform the data */
DATA WORK.CLR_CALL_ACTIV_HIST_2016_0;
	/* Load in work table containing the pre-transformed file */
	SET WORK.CLR_CALL_ACTIV_HIST_2016_0;

	/* Transformations */
	'CHANNEL-ID-NUM'N			= INPUT(STRIP('CHANNEL-ID'N), BEST32.);

	IF MISSING('CHANNEL-OBJ-ID'N) OR UPCASE('CHANNEL-OBJ-ID'N) = "N/A" THEN
		'CHANNEL-OBJ-ID-NUM'N = .;
	ELSE
		'CHANNEL-OBJ-ID-NUM'N = INPUT(STRIP('CHANNEL-OBJ-ID'N), BEST32.);

	'GROUP-ID-NUM'N				= INPUT(STRIP('GROUP-ID'N), BEST32.);
	'INTERACTION-ID-NUM'N		= INPUT(STRIP('INTERACTION-ID'N), BEST32.);


	'DATE'N = INPUT(SUBSTR('TRANS-ACCEPT-TIME'N, 1, 10), YYMMDD10.);
		FORMAT 'DATE'N YYMMDD10.;
	'TIME'N	= INPUT(SUBSTR('TRANS-ACCEPT-TIME'N, 12, 8), TIME8.);
		FORMAT 'TIME'N TIME8.;
	'DATETIME'N = DHMS('DATE'N, HOUR('TIME'N), MINUTE('TIME'N), SECOND('TIME'N));
		FORMAT 'DATETIME'N DATETIME20.;

	'TRANS-POST-PROC-TIME-NUM'N	= INPUT(STRIP('TRANS-POST-PROC-TIME'N),	BEST32.);
	'TRANS-PROC-TIME-NUM'N		= INPUT(STRIP('TRANS-PROC-TIME'N), 		BEST32.);
	'TRANS-REC-NUM-NUM'N		= INPUT(STRIP('TRANS-REC-NUM'N), 		BEST32.);
	'TRANS-TOTAL-TIME-NUM'N		= INPUT(STRIP('TRANS-TOTAL-TIME'N), 	BEST32.);
	'TRANSACTION-ID-NUM'N		= INPUT(STRIP('TRANSACTION-ID'N), 		BEST32.);
RUN;

/* Drop columns */
DATA WORK.CLR_CALL_ACTIV_HIST_2016_1;
	SET WORK.CLR_CALL_ACTIV_HIST_2016_0;

	DROP	'CHANNEL-ID'N
			'CHANNEL-OBJ-ID'N
			'GROUP-ID'N
			'INTERACTION-ID'N
			'TRANS-ACCEPT-TIME'N
			'DATE'N
			'TIME'N
			'TRANS-POST-PROC-TIME'N
			'TRANS-PROC-TIME'N
			'TRANS-REC-NUM'N
			'TRANS-TOTAL-TIME'N
			'TRANSACTION-ID'N
	;
RUN;

/* Rename columns */
DATA WORK.CLR_CALL_ACTIV_HIST_2016_2;
	SET WORK.CLR_CALL_ACTIV_HIST_2016_1;

	RENAME	'CHANNEL-ID-NUM'N 			= 'CHANNEL-ID'n
			'CHANNEL-OBJ-ID-NUM'N		= 'CHANNEL-OBJ-ID'N
			'GROUP-ID-NUM'N				= 'GROUP-ID'N
			'INTERACTION-ID-NUM'N		= 'INTERACTION-ID'N
			'DATETIME'N					= 'TRANS-ACCEPT-TIME'N
			'TRANS-POST-PROC-TIME-NUM'N	= 'TRANS-POST-PROC-TIME'N
			'TRANS-PROC-TIME-NUM'N		= 'TRANS-PROC-TIME'N
			'TRANS-REC-NUM-NUM'N		= 'TRANS-REC-NUM'N
			'TRANS-TOTAL-TIME-NUM'N		= 'TRANS-TOTAL-TIME'N
			'TRANSACTION-ID-NUM'N		= 'TRANSACTION-ID'N
	;
RUN;

/* Load final table */
DATA WORK.CLR_CALL_ACTIV_HIST_2016;
	SET WORK.CLR_CALL_ACTIV_HIST_2016_2;
RUN;

Here is my Snowflake connector code:

libname NUC_LIB snow
	SERVER		= "xxxx"
	USER		= "xxxx"
	PASSWORD	= "xxxx"
	DATABASE	= "xxxx"
	SCHEMA 		= "xxxx"
	ROLE 		= "xxxx"
	WAREHOUSE 	= "xxxx"
;

DATA NUC_LIB.CLR_CALL_ACTIV_HIST_2016
	(
	  BULKLOAD              = YES
	  BL_INTERNAL_STAGE     = "user/nucleus"
	  BL_COMPRESS           = YES
	  BL_DELETE_DATAFILE    = YES
	  DBCOMMIT              = 1000000
	);

	SET WORK.CLR_CALL_ACTIV_HIST_2016;
RUN;

Essentially, I need the DATETIME columns from SAS to be DATETIME columns in Snowflake.

[SASKiwi]

If you don't use the BULKLOAD option do DATETIMEs work?