How to create entry in "cmn_schedule_span" table through script.

[RAHUL Khanna1]

Hello Guys, 

I am trying to create an entry in "cmn_schedule_span" table through script. But I am facing issue with the start date and end date . 

The dates are not getting properly populated (may be some issue with time zone), also all day is not setting to true .

Can any one of you guide me .

var holidayName = "Test-4";
var schedule = '0e14cbacdb9e27041727f1c51d96193a';
var type = 'exclude';
var show_as = 'busy';
var repeat = 'NULL_OVERRIDE';
var allDay = 'true';  // checked with/without codes
var date = "03-06-2018" // I am creating a date fied on catalog item this would be input for this field 

var gr = new GlideRecord('cmn_schedule_span');
gr.initialize();
gr.setValue('name', holidayName);
gr.setValue('schedule', schedule);
gr.setValue('type', type);
gr.setValue('show_as', show_as);
gr.setValue('all_day', allDay );
gr.setValue('repeat_type', repeat);
gr.start_date_time =date.toString();
gr.end_date_time = date.toString();
p = gr.insert();
gs.log( p); //  showing null most of the time 

[Zach Wehrli]

Here was my solution. Hope this helps someone else.

Side Note: I also replied on this post about how I query spans with a time:

What is the correct method to query Schedule Date/time fields in cmn_schedule_span?

I created a record producer catalog item restricted to those allowed to create new blackout schedule entries.

The user inputs the dates into standard date form fields. A client script converts the input into the needed string format on a hidden field that is mapped to the the date time fields on the table.

Our blackouts always cover full days, so I have the all_day field hidden and set to true by default.

A simple script ensures the name/title of the blackout is in a consistent format. You can see an example input in the last image.

The Variables

The Client Scripts

Set start value script (end value script identical. only changes field names)

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }
    
    var startDateTime = new Date(newValue);
    startDateTime.setSeconds(0);
    startDateTime.setMinutes(0);
    startDateTime.setHours(0);

	var formattedDate = startDateTime.toISOString().replace(/[-:]/g, '').split('.')[0] + 'Z';


    g_form.setValue('start_date_time', formattedDate);
}

Update Functions Script (called by Trigger update onchange scripts)

Should probably move the start/end value scripts into a function called here.

function onLoad() {
    //Type appropriate comment here, and begin script below
	scheduleid='';

	getValues = function() {
        bs = g_form.getValue('service_blackout');
        env = g_form.getValue('environment');
        reason = g_form.getValue('blackout_reason');
	};

    updateFilter = function() {
        getValues();
        envquery = '';
		bsquery = '';
		if (!bs) {
            bsquery = 'nameSTARTSWITHALL';
        } else {
			bsquery = 'nameLIKE' + bs;
		}
		
		if (!env) {
            envquery = 'nameNOT LIKEproduction^nameNOT LIKEstage^nameNOT LIKEintegration^nameNOT LIKEqa^nameNOT LIKEdevelopment^nameNOT LIKEux';
        } else {
			envquery = 'nameLIKE' + env;
		}
		query = bsquery + "^" + envquery;

        filter = g_list.get('schedule');
		filter.setQuery(query);
    };

	updateTitle = function() {
		getValues();
		title = bs + ' ' + env + ' ' + reason + ' Blackout';
		g_form.setValue('name', title);
	};

	updateFilter();
}

The Catalog Item Example

Ignore the help text on the Environment field. It requires them to select one.