Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 158 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

While fact-checking this timestamp-related feature-request, I noticed that timestamps in the Data Explorer are displayed with hours in 12-hour format, even though no AM/PM is displayed. So 2009-01-01 01:00:00 could be 1:00 AM or 1:00 PM. See this sample query:

select top 200 creationdate, datepart(hh, creationdate) as hour
from users
where datepart(yyyy, creationdate) = 2009
order by creationdate​

And the result:

Note that the time is actually stored correctly internally, as ordering works correctly, and the DatePart function works correctly. I've verified that this occurs for Users.CreationDate, Comments.CreationDate, and Posts.CreationDate. I'm guessing it happens on every timestamp field.

share|improve this question
Patch anyone :) ? – waffles Aug 2 '10 at 23:28
@waffles: Is it an environment problem, or a code problem? We can only fix the latter. – Jon Seigel Aug 3 '10 at 16:44
up vote 2 down vote accepted

As far as I can tell, this isn't exactly a bug, but it definitely needs to be resolved.

Here's a simplified query that demonstrates the problem:

    CONVERT(datetime, 'Jan 1 2010 1:00 AM'),
    CONVERT(datetime, 'Jan 1 2010 1:00 PM')

The resulting output is:

------------------- ------------------- 
2010-01-01 01:00:00 2010-01-01 01:00:00 

(1 row(s) affected)

which is clearly not good.

Looking at the source code (App/StackExchange.DataExplorer/Helpers/QueryResults.cs:172), it appears that for date types, all that's happening for display is calling .ToString() on the object. So it may be that the system locale is set up with a weird date format.

I'm not familiar with the Azure platform, but this can be fixed by either (a) fixing the system locale settings (probably a good idea anyway if possible), or (b) explicitly specifying a format string with all the required date/time components.

share|improve this answer
i would say it's a bug if it is displaying in 12-hour format without displaying am/pm, as there's no way of knowing what "01:00:00" is, and nothing to indicate that it should be interpreted as anything other than 1:00 AM. – Kip Aug 2 '10 at 17:01
@Kip: Agreed, it's definitely showing the wrong value, whatever word we use to describe the issue. – Jon Seigel Aug 2 '10 at 18:23

Fixed in latest, should be deployed shortly:

col = date.ToString("yyyy-MM-dd H:mm:ss");
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .