GildeRecord - Cast a field as date? - is it possible

[Natalie Timbs]

Hey,

Our import sets import the data into staging tables with a data type of string and we the want to do a query over the data to remove older entries based upon on a date field, but since it's a string we can't properly use gliderecord.orderBy.

1) Is there a way to cast the field as a date with in a gilderecord query?

2) Is there a way to set a default data type of a dynamically create table that is built by an excel file?

    The excel file does have the correct data formatter applied, but that doesn't seem to carry over into the data source    / import sets area.

Note: We are unable to set a default data type in the staging table, due to the staging tables being dropped every 7 days. 

[AshishKM]

Hi @Natalie Timbs ,

About stage table drop , I think you are referring the stage table data deletion not the table.

Any issue to configure date type column for incoming date value coming from xls column. 

During the transform map process , this query can apply orderby query over staging table record [ on before ].Just a thought based on given details understanding.

Can you share , staging table column and date format from target table. 

-Thanks

[Natalie Timbs]

we have a table clean up script that is automated and it runs every 7 days and looking at the 3rd line for the bottom it talks about dropping the table. we have setup a test to see if it is indeed dropping the table. but the people who wrote the script have stated that it does indeed drop the table.

for (var index = 0; index < importSetTables.length; index++) {
var stagingTable = importSetTables[index];
// making sure its the correct table
if (regxPat.test(stagingTable)){
this.table_label = stagingTable;
this.drop_table = true;
}
}


   _dropTable : function() {
      if (this._isImpTable())
         return;

      this._log('Removing table structure, dictionary, and associated documentation');
      var db = GlideScriptSystemUtilDB;
      // remove list forms
      db.removeUIList(this.table);

      // remove form sections and ui elements
      db.removeUISection(this.table);

      // drop the table
      gs.dropTable(this.table);
   },

2) the date field is correctly setup in the excel file (see screenshot 1)

3) We can't do an orderby over the records in the staging table as the dates are being imported as string.

This is why I am asking if we can do a cast on a field inside of a gilderecord query.

[AshishKM]

Check this thread for some reference 

https://www.servicenow.com/community/developer-forum/converting-to-date-format/m-p/2372753#:~:text=I....

so when this new staging table created , every time when new xls uploaded.

and you are trying to delete some record from target table during the transform time but before the process.

[Natalie Timbs]

We can convert it to a date doing the following

while (gruser.next()) {
//we now need to set the join date. only do this to a few records that need it to be done to. this should save some coding.
var _user_joinedDateStr_loop = gruser.u_det_date_jnd;
var _user_joinDate_loop = new GlideDateTime(_user_joinedDateStr_loop);
gruser.u_date_jnd = _user_joinDate_loop;
gruser.update();
}
//start our final loop. this has the join date filled out. we know the employee has more an 1 record
gruser = new GlideRecord('xxxxx');
gruser.addQuery('sys_import_set', _setid);
gruser.addQuery('xxx', xxx;
gruser.addNullQuery('u_is_completed');
gruser.orderByDesc('u_date_jnd');

but we need to store the converted date into a field and then sort it.. which we have kind of done. we manually created a new field on the staging table (u_date_jnd) and set it's date type as date.

This all works.  but as is in the staging table, and it is deleted every 7 days , this solution will not work. hence why I am asking to cast the date string as date in the gilderecord query.

For example doing something like this in SQL

select top 1 cast (p.JoinDate as date) from (
select '2023-05-12' JoinDate
union
select '2023-06-12' JoinDate
union
select '2023-07-12' JoinDate
) p
order by cast (p.JoinDate as date) desc

this converts the string date into a date and then sorts it (note: not the best example)