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

I need to format a set of dates in S开发者_开发百科QL 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?

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,'')

DECLARE @SampleDate DATETIME2(3) = '2020-07-05 23:59:59';
SELECT CONVERT(VARCHAR(20), CONVERT(DATETIMEOFFSET, @SampleDate), 127);
--results: 2020-07-05T23:59:59Z 

You can parse C# output in SQL using below:

SELECT CONVERT(DATETIME, CONVERT(DATETIMEOFFSET,'2017-10-27T10:44:46Z'))

Use C# to generate this using the following:

string ConnectionString = "Data Source=SERVERNAME; Initial Catalog=DATABASENAME; Persist Security Info=True; User ID=USERNAME; Password=PASSWORD";

using(SqlConnection conn = new SqlConnection(ConnectionString))
{
    DateTime d = DateTime.Now;

    string Query = "SELECT CONVERT(DATETIME, CONVERT(DATETIMEOFFSET,'" + d.ToString("yyyy-MM-dd") + "T" + d.ToString("HH:mm:ss") + "Z'))"

    conn.Open();

    using(SqlCommand cmd = new SqlCommand(Query, conn))
    {
        using(SqlDataReader rdr = cmd.ExecuteReader())
        {
            if(rdr.HasRows)
            {
                while(rdr.Read())
                {
                    for(int i; i < rdr.length; i++)
                    {
                        Console.WriteLine(rdr[0].ToString());

                    }

                }

                //DataTable dt = new DataTable(); dt.Load(rdr); //Alternative method if DataTable preferred

            }

        }

    }

}

select left(convert(varchar(30),getdate(),126)+ '.000',23)    

Try this

 SELECT STUFF(
     CONVERT(datetime2(0), GETDATE(), 126)      
    AT TIME ZONE 'US Eastern Standard Time'   
    ,11,1,'T')

on MSSQL

SELECT FORMAT( GETDATE(),'yyyy-MM-ddTHH:mm:ss.ms zzzz')