why is my 126 datetime not coming back with a T in the middle?

Question

I've decided that I'll use 8601 datetimes for all the datetimes that I return from my app. Suddenly, in one particular proc, getdate() isn't returning a datetime with a T in the middle. I should also mention that I'm converting the set containing a datetime to XML using FOR XML PATH. Typically when I convert a table containing datetime to xml I get 8601 formatted dates. But in one case, I'm not.

select (cast(getdate() as datetime)) -- returns 2010-01-25 10:13:46.033

So I directly converted it like so:

select convert(datetime, getdate(), 126) -- returns 2010-01-25 10:14:35.923

But if I cast it to an nvarchar I get the T!!

SELECT CONVERT(NVARCHAR(30), GETDATE(), 126) -- returns 2010-01-25T10:15:29.633

What's even stranger to me is that if I select several versions of this with a union, the T version disappears. But selecting without the union, the T version (last one) remains.

-- returns 4 rows of 2010-01-25 10:15:57.333
select getdate() union all 
select (cast(getdate() as datetime))  union all
select convert(datetime, getdate(), 126)    union   all
SELECT CONVERT(NVARCHAR(30), GETDATE(), 126) 

I really have no idea what could cause this to happen. I thought 8601 dates were locale-independent, so I don't think it's anything like that.

Reference ("yyyy-mm-ddThh:mi:ss.mmm" for a 126): http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 1

When you convert to datetime, you're not choosing a format - date, time, and datetime are all stored in an internal format. Only when you convert to a string (char, varchar, nvarchar) does the format matter to the output. In the other cases the format is being chosen by SQL Server implicitly, and it happens to be close but not identical to 8601.

Answer 2

GETDATE() already returns DATETIME, so this expression:

CONVERT(datetime, getdate(), 126)

does nothing.

You may substitute any other format option of even omit the CONVERT portion: the result will be the same.

The string representation of the DATETIME returned is chosen by the client, not the server.

Its internal representation is a 8-byte integer, with the first 4 bytes denoting number of days from 1900-01-01, and the second 4 bytes denoting the number of 1/300 seconds from midnight.

When you convert to an NVARCHAR, then the server makes the text representation and returns it as is. In this case, you see a T substituted by the server.

Answer 3

operator precedence, in your union everything gets cast to a datetime since that is the first row. If you want the T then you need to cast to varchar but again you can't do this in a union since mixed datatypes will be cast to the same datatype (if it is possible)

This includes the T, a datetime itself is stored as 2 integers and has nothing to do with this

SELECT CONVERT(VARCHAR(30), GETDATE(), 126) 

Answer 4

You are mixing up datetime values with formatted strings.

The first two examples doesn't return the date formatted in any way at all, it's just a datetime value. How the value is formatted into text is decided by how you display the value after getting it from the database. If you lived in a different country so that your default culture settings were different, the date could for example be displayed as 1/25/2010 10:14 AM instead.

In the second example the format parameter (126) is ignored, as there is no formatting or parsing involved when converting from a datetime value to a datetime value.

The third example formats the datetime value into a string before it's returned from the database, that's why you get it in the format that the database uses.

If you use a union with difference data types, it's the type precedence that decides the type of the result. The datetime type has higher precedence than nvarchar, so it attempts to convert the nvarchar values to datetime.