21

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?

Machavity
  • 30,841
  • 27
  • 92
  • 100
carrot_programmer_3
  • 915
  • 3
  • 14
  • 28

6 Answers6

21

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,'')
Bork Blatt
  • 3,308
  • 2
  • 19
  • 17
  • 6
    Agreed that's what the doc says but `SELECT CONVERT(VARCHAR(50), GETDATE(), 127)` returns `2011-05-24T11:39:30.330` for me on SQL Server 2008. (Seems to be the same as 126 without any `Z`?) – Martin Smith May 24 '11 at 10:39
  • Milli seconds is not asked by OP. The result is 2011-05-24T16:14:33.830Z – Pankaj May 24 '11 at 10:41
  • I agree Pankaj, milliseconds weren't in the question sepc, but according to the docs this is the only format that includes time zone. From 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.' – Bork Blatt May 24 '11 at 10:44
  • 1
    +1 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,'')` – Martin Smith May 24 '11 at 10:45
  • How about time zone offset? `;WITH c AS ( SELECT DATEDIFF(HOUR, GETUTCDATE(), GETDATE()) HH ) SELECT STUFF(CONVERT(VARCHAR(50), GETUTCDATE(), 127) + + CASE WHEN C.HH > 0 THEN '+' WHEN c.HH < 0 THEN '-' ELSE '' END + CASE WHEN c.hh < 9 THEN '0' ELSE '' END + CAST(C.HH AS NVARCHAR) + ':00', 20, 4, '') FROM c` – Dalex May 24 '11 at 12:13
  • @abdul-hannan-ijaz answer https://stackoverflow.com/a/71620320/481816 has an example of using the built-in function FORMAT https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver16 that was added in SQL Server 2012. The new function allows for more human-readable format strings and may be preferable to some users. – Dude0001 May 27 '22 at 08:56
8
DECLARE @SampleDate DATETIME2(3) = '2020-07-05 23:59:59';
SELECT CONVERT(VARCHAR(20), CONVERT(DATETIMEOFFSET, @SampleDate), 127);
--results: 2020-07-05T23:59:59Z
Jeremy Giaco
  • 342
  • 3
  • 5
  • 1
    This is the real answer to the original question. Which was how to get the time zone showing when using datetime style 127 in Convert. – Mr McGoo Sep 02 '20 at 00:29
6

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

            }

        }

    }

}
WonderWorker
  • 8,539
  • 4
  • 63
  • 74
Bek Raupov
  • 3,782
  • 3
  • 24
  • 42
1
select left(convert(varchar(30),getdate(),126)+ '.000',23)
kiko1875
  • 11
  • 1
  • 1
    While this code may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanation, and give an indication of what limitations and assumptions apply. – Makyen Dec 18 '17 at 19:23
  • In fact, it's a code that I made it's been a long time. Even I, I don't know how I found it, but it works 100%. That's it. Thank you. – kiko1875 Dec 19 '17 at 08:51
1

Try this

 SELECT STUFF(
     CONVERT(datetime2(0), GETDATE(), 126)      
    AT TIME ZONE 'US Eastern Standard Time'   
    ,11,1,'T')
Geraldo Diaz
  • 346
  • 5
  • 7
1

on MSSQL

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

Abdul Hannan Ijaz
  • 794
  • 6
  • 11