Convert Date/Time (down to the millseconds) into a Variable

[#1]

Hello,

I am trying to convert a date/time down to the milliseconds in order to insert into an SQL statement that I am using in my JSL script. The typical method I use to do so usually creates the variable (date) with single quotes around it. However, when attempting the same method to my date/time which includes milliseconds it adds two single quotes to each end of the variable. Could someone assist in potentially solving my issue, and/or helping pointing me in a different direction for the result I am looking for?

dt1 = Current Data Table ();

dt1 << New Column ("Year", Formula(Year(Today())));  
dt1 << New Column ("Month", Formula(Month(Today()))); 
dt1 << New Column ("Day", Formula (Day(Today()))); 

dt1 << New Column ("UpdateDate", Formula (Char( :Year ) || "-" || Char( :Month ) || "-" || Char( :Day ) || "-" ||
"12.20.00.000000"));

dt2 = dt1 << Summary (
	Group (:UpdateDate), 
	Link to Original Data Table (0)
); 

dt2 = current data table (); 

dt2 << Delete Columns ("N Rows"); 

date_id = dt3:UpdateDate << Get Values; 
date_ids = "'"||date_id||"'"; 

The result I would like would be like this --> Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 420 StartFragment: 314 EndFragment: 388 StartSelection: 314 EndSelection: 314

'2021-3-4-09.20.00.000000'

Any help is appreciated!

[#2]

Hi @Sully,

I am not exactly sure which part of your script wasn't behaving correctly as it did not run out of the box.  I made a few changes, can you see if this does what you need it to?

Names default to here(1);

dt1 = New Table( "Date test",
	Add Rows( 1 ),
	New Column( "Column 1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [] ) )
);

dt1 << New Column ("Year", Formula(Year(Today())));  
dt1 << New Column ("Month", Formula(Month(Today()))); 
dt1 << New Column ("Day", Formula (Day(Today()))); 

//Original UpdateDate column
dt1 << New Column ("UpdateDate", Character, "Nominal", Formula (Char( :Year ) || "-" || Char( :Month ) || "-" || Char( :Day ) || "-" ||
"12.20.00.000000"));

//Option 1 - adjusting your script
dt2 = dt1 << Summary (
	Group (:UpdateDate), 
	Link to Original Data Table (0)
); 

dt2 << Delete Columns ("N Rows"); 

//Get the UpdateDate and add single quotes to each line
date_id = dt2:UpdateDate << Get Values; 
date_ids = list();
write("List of date_ids:");
for(i=1, i<= n items(date_id), i++,
	date_ids[i] = "'" || date_id[i]  || "'"; 
	write( "\!n" || date_ids[i] );
);

//Option 2 - include quotes in original column formula and then use associative array to find unique values
dt1 << New Column ("UpdateDateQuoted", Character, "Nominal", Formula ("'" || Char( :Year ) || "-" || Char( :Month ) || "-" || Char( :Day ) || "-" ||
"12.20.00.000000'"));

date_ids_2 = associative array(dt1:UpdateDateQuoted) << get keys;
write("\!n\!nDateID from associative array:\!n" || date_ids_2[1];)