Wrong column date format in SQL Server

Question

I'm inserting some data from my C# program into SQL Server 2012. I have column in my table that is datetime type. My C# code:

string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
// returns 2016-04-11 11:47:05.535

When I debug my application I can see that date is in correct format (same as above). But when I look into my database, this date is:

2016-11-04 11:47:05.537

Month and day are swapped. Why is this happening?

[Sql server does not store date display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) When you use SSMS to select a datetime value it is displayed as a string in the default display format of the database. try this `SELECT DATENAME(MONTH, YourDateTimeColumn)` and see if you get the correct month. — Zohar Peled, Apr 11 '16 at 09:52
Stop working with *strings*. They're the cause of your formatting issues. Use `DateTime` in C# and `datetime` (or `datetime2`) in SQL Server. Use parameters when sending the data from C# -> SQL Server. Just make sure you're using datetime datatypes throughout and you will not *have* formatting issues. — Damien_The_Unbeliever, Apr 11 '16 at 09:56
@L-Three - but they're using `string` in the C#. Once you've converted to string (in C# *or* SQL), you've opened yourself up to formatting issues. — Damien_The_Unbeliever, Apr 11 '16 at 10:21
No, that is not an issue per se; as you need a string if you want to display it in a view. Then you need to convert into a string, using the correct culture; and vise versa. — L-Four, Apr 11 '16 at 10:23
@L-Three - but the issue here is, I guarantee, that there's a string conversion between a *perfectly good* .NET DateTime value and a SQL Server `datetime` value. Without that string conversion, the OP would *not* be reporting that "SQL" had switched the day/month values. You're the only person who seems to be mentioning display. — Damien_The_Unbeliever, Apr 11 '16 at 10:25
Yes, the exact same issue will be happening if the culture is not set properly. And I say display because this is a valid reason to use strings, not a wrong mindset by default which is what you are saying. — L-Four, Apr 11 '16 at 10:26
@L-Three - no, it wouldn't. .NET's datetime type stores a single count of 100 nanosecond intervals since some fixed point in the past. It contains no formatting or culture information. SQL Server's datetime type stores the number of (fractional) days since 1st Jan 1900 with no formatting or culture information. ADO.NET understands how to translate between these two and doesn't use any culture or formatting information. You can *only* introduce issues if you (the programmer) start converting values into or out of strings. — Damien_The_Unbeliever, Apr 11 '16 at 10:30
As said, it's valid to convert to string if you need to display it. That's valid behavior. — L-Four, Apr 11 '16 at 10:31
@L-Three: Damien is is correct. How you display the date is basically irrelevant. This issue revolves around mixing string and datetime datatypes, which shouldn't happen. Pretty much every framework (and especially those used with .Net & SQL Server) supports the datetime / timestamp datatype. So if the OP *wants* to display, or use a textbox where the date is entered in a specific string format, they can do that. But that string needs to be validated before being converted to datetime in the code, *before* being passed onto SQL Server. Stuff like this in OP just shouldn't happen. — Kahn, Apr 11 '16 at 10:41

score 6 · Accepted Answer · edited Jul 29 '20 at 18:28

6

You have a bad habit to kick as choosing the wrong data type.

You should never store your DateTime values with their string representations. SQL Server's datetime type mapped with System.DateTime in .NET Framework side. That's why you should insert your DateTime.Now value directly with a parameterized query.

But be aware of The case against DateTime.Now as well.

On the other hand, let's analyze your examples..

2016-04-11 11:47:05.535
2016-11-04 11:47:05.537

Ignoring datetime type will round millisecond part to .000, .003 or .007, can we %100 sure that those values are exactly same based on just their representations?

No, we can't.

Those representations depends on sql server's collation and culture settings. That's why your 2016-11-04 11:47:05.537 might even be 4th of November, not 11th of April.

edited Jul 29 '20 at 18:28

Community

1
1

answered Apr 11 '16 at 10:12

Soner Gönül

97,193
102
206
364

Why do you think he stores it as string? The data type in the database is DateTime. This is a culture problem, not data storage problem. – L-Four Apr 11 '16 at 10:18
1

@L-Three - no, this is an "everything has to be a string at some point" problem. It's getting the OP out of *that* mindset that will fix the issue. – Damien_The_Unbeliever Apr 11 '16 at 10:20
@L-Three Since OP writes `date` variable in his code, I think this is the inserted value which is `string`. Looks like OP'S string has valid format to insert in `datetime` type in SQL Server. But we _can't_ know that which value will be inserted since "format" concept might generate different results for different collation/culture settings. This does not seems a `CultureInfo` problem because OP's "format" and the result value has same formats. You _can't_ be sure about this `2016-11-04` value is `4th of November` or `11th of April` _just_ based on it's representation. – Soner Gönül Apr 11 '16 at 10:20
1

There is no issue if you need it formatted as a string to be displayed in a UI. I don't see a wrong mindset here per se, if date is used in a view for example. But you have to take into account culture. – L-Four Apr 11 '16 at 10:21

Steve Moore · Answer 2 · 2016-04-11T15:00:57.817

1

I am not convinced there is an actual problem here, the reason why it is happening is that dates are displayed differently in different countries. Try to insert a date which cannot be valid if the values are switched e.g. 22/11/2015.

In .NET you can manage this display using CultureInfo MSDN Link to achieve a UK date/time format as standard behaviour you would use it like this:

CultureInfo en = new CultureInfo("en-GB");

Hope this helps.

edited Apr 11 '16 at 15:00

answered Apr 11 '16 at 10:06

Steve Moore

168
6

The problem is not date format in C# but on SQL Server. – r9s Apr 11 '16 at 10:09
This is what I am trying to say, I am not convinced there is an actual problem. SQL is storing the date in US format and you are displaying the date in GB format. SQL is not designed to be a presentation layer this is something that should be handled in your application. – Steve Moore Apr 11 '16 at 10:12
To test my theory run this statement in your database and you will see the date displayed correctly. - `DECLARE @d DATETIME = '04/11/2016'; SELECT FORMAT ( @d, 'd', 'en-GB' ) AS 'GB English Result'` – Steve Moore Apr 11 '16 at 10:23
2

Actually, "dates are stored differently in different countries" is **completely wrong.** Dates are displayed differently in different countries but they are stored exactly the same, regardless of local settings. – Zohar Peled Apr 11 '16 at 11:19

Wrong column date format in SQL Server

2 Answers2

No, we can't.