0

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?

Improve this question
11
  • 3
    Sql server does not store date display format. 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. Commented Apr 11, 2016 at 9:52
  • 4
    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. Commented Apr 11, 2016 at 9:56
  • 2
    @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. Commented Apr 11, 2016 at 10:21
  • 2
    @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. Commented Apr 11, 2016 at 10:25
  • 2
    @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. Commented Apr 11, 2016 at 10:30

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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-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.
@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.
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.
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.

Improve this answer

4 Comments

The problem is not date format in C# but on SQL Server.
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.
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'
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.