I am using the CLI Dataloader to successfully extract records from Salesforce as part of an interface into Back Office Systems. I have a query, a bit like this:

SELECT ID , ClosedDate, createdDate,date_resident__c 
FROM case WHERE status = 'Closed' 
AND RecordType.Name = 'SPD123' 
AND processed__c != TRUE

Unfortunately the Back Office Staff don't like the date formats of the created/closed date fields (timezone) or the custom date field (yyyy-mm-dd).

Is there a way I can extract the data using SOQL to reformat the timezone and date fields to the formats (DD/MM/YYYY hh:mm:ss for timezone and DD/MM/YYYY for date). I have tried some various SOQL functions but I know it doesn't support the full range normal SQL does.

If there isn't a way to do it in SOQL presumably the only way is batfile (I am already doing this for a few reasons so it isn't the end of the world).

Thanks, N


There's no way to control the output of data values in SOQL that would help you. You'll have to do your own post-processing.

  • Thanks - saves some unnecessary fretting!!! I can see why tools like pentaho maybe necessary, only scratching the surface with our salesforce implementation and interfacing... – n34_panda Aug 15 '16 at 16:02
  • @n34_panda Yes, ETL tools (Extract-Transform-Load) usually provide the ability to change the input data before outputting data (hence the "transform" in ETL). The data loader is actually an incredibly simple program without many bells and whistles. – sfdcfox Aug 15 '16 at 16:04
  • Probably why I've liked it so far!!! – n34_panda Aug 15 '16 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.