change Date format in exported file

[bobm]

I'm exporting some patient data and looking to set the date format as mmddyyyy
Postgres stores the data as yyyy-dd-mm 00:00:00:0
I'm using a straight ds export to a comma delimited txt file

Code: Select all

      patients.loadAllRecords();
      var ds = databaseManager.convertToDataSet(patients,['patient_id','last_name','first_name','address','address2','city','state','zipcode','dob'']);
      var str=ds.getAsText(',','\n',"",true);
      
      scopes.file.writeTXTFile(patientsFromCloud,str);

Thank you,
Bob

[ROCLASI]

Hi Bob,

You can loop through the DataSet and format (utils.dateFormat()) the date while putting the result in an array.
But it's probably more efficient to simply use a SQL query and format the date in there like this (assuming the table is named 'patients' and all records are saved to the database):

Code: Select all

// replace 'connectionName' with the proper value
var ds = databaseManager.getDataSetByQuery("connectionName",
    "SELECT patient_id, last_name, first_name, address, address2, city, state, zipcode, to_char(dob, 'MMDDYYYY') AS dob FROM patients",
    null, -1);
if ( ds.getException()) {
    throw ds.getException();
}
// get the result and write it straight to the file
scopes.file.writeTXTFile(patientsFromCloud, ds.getAsText(', ','\n', "", true));

See https://www.postgresql.org/docs/current ... tting.html for all the formatting options.

Hope this helps.

[bobm]

Thank you again for you help on this. needed to set a couple args but worked great!