CODEWITHSUNDEEP
sql-servert-sql
carrot_programmer_3
carrot_programmer_3

Reputation: 955

Format date as yyyy-MM-ddThh:mm:ssZ

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

yyyy-MM-ddThh:mm:ssZ

I can't 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 Server query?

Upvotes: 25

Views: 123488

Answers (6)

Bek Raupov
Bek Raupov

Reputation: 3777

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
            }
        }
    }
}

Upvotes: 6

Abdul Hannan Ijaz
Abdul Hannan Ijaz

Reputation: 844

on MSSQL

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

Upvotes: 1

Jeremy Giaco
Jeremy Giaco

Reputation: 402

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

Upvotes: 10

Geraldo Diaz
Geraldo Diaz

Reputation: 356

Try this

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

Upvotes: 1

kiko1875
kiko1875

Reputation: 21

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

Upvotes: 2

Bork Blatt
Bork Blatt

Reputation: 3368

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

Upvotes: 23

Related Questions