Convert dates to Salesforce format in T-SQL

When preparing csv files for import through the data loader, it is best practice to format the datetime values in the full yyyy-mm-ddThh.mm.ss.sssZ format required by Salesforce.com. This prevents any complications with Data Loader or other migration tools which may have different settings depending on the environment or installed software (for example, dates can be scrambled if your data loader has 'Use European Date Format' checked, but your client's data loader does not.

In SQL Server there are a number of native style formats available, and the closest I have found to SFDC uses ISO8601 format yyyy-mm-ddThh:mm:ss.

CONVERT(varchar,o.CloseDate,126) AS CloseDate_ISO8601Format

Because I use text-based csv files to pass information up to the Salesforce cloud, I don't have to worry too much about data types, so in this case the simplest method for formatting this date is to concatenate the ISO8601 text with an appended 'Z'.

CONVERT(varchar,o.CloseDate,126)+'Z' AS CloseDate_SFDC

See also my earlier blog about conversion options for dates, and this one about date fields.

For more information on date formats and date literals, see the developer documentation.