We want to send some date field data up to our Elasticsearch instance in the format yyyy-mm-ddThh:mi:ss.mmmZ. The source of the data is a DATETIME data type column in our SQL Server 2008 R2 database.
According to BOL to get the format we need we need to run the following (in this example I’m just using the GETDATE() function rather than selecting an actual value from a table.
SELECT CONVERT(VARCHAR(50), GETDATE(), 127);
When I run this I don’t get the “Z” character at the end.
In BOL there is a comment that says “The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or – direction. For example: 2006-12-12T23:45:12-08:00.”
The reason the original query doesn’t return the “Z” is there is no offset data in the DATETIME data type.
We need to add the offset to the data by casting it as a DATETIMEOFFSET data type before converting to the string, i.e.
SELECT CONVERT(VARCHAR(50), CAST(GETDATE() AS DATETIMEOFFSET), 127);
NB. As we are using GETDATE() you will see the millisecond included in the result but if your original data is only precise to the nearest second then the millisecond value won’t appear. If you have got millisecond data then you could use the STUFF function after conversion to the string to remove them.