sql server date in the format yyyy-MM-ddThh:mm:ssZ

Question

I need to format a set of dates in SQL server to the following format..

yyyy-MM-ddThh:mm:ssZ

I cant seem to find how to format the date with the T and Z parts included in the string

Any ideas how to achieve this format in a SQL query?

Answer 1

According to the SQL Server 2005 books online page on Cast and Convert you use date format 127 - as per the example below

CONVERT(varchar(50), DateValueField, 127)

SQL Server 2000 documentation makes no reference to this format - perhaps it is only available from versions 2005 and up.

Note on the time zone added to the end (from note 7 in the docs): 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.

Thanks to Martin for this note: You should be able to use STUFF to remove the miliseconds as these will be in a fixed position from the left of the string. i.e.

SELECT STUFF(CONVERT(VARCHAR(50),GETDATE(), 127) ,20,4,'')