CODEWITHSUNDEEP
.netsql-serverdatetime
Duncan Gravill
Duncan Gravill

Reputation: 4682

DateTime format does not match format in SQL Server

The format of DateTime.UtcNow.ToString() is - DD/MM/YYYY HH:MM:SS

The format that SQL Server Express expects is - YYYY/MM/DD HH:MM:SS

The format of (SqlDateTime)DateTime.UtcNow is - DD/MM/YYYY HH:MM:SS

As far as I know both are UK formats. I am in the UK and running the code and SQL Server Express on the same machine.

I know I could specify the format in the ToString() method but that doesn't answer the underlying problem. Why does SQL Server Express expect a different format when it is running on the same machine?

What is the best way to resolve this mismatch so that it also works when I publish to the server (which might not have this problem)?

Should I synchronize the date formats on my machine and assume things will be fine when I publish? If so how do I do this? Or should I handle it in code by somehow detecting the date format of SQL Server Express and making my code produce the same format? If so how do I do this?

Upvotes: 2

Views: 6707

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

Use a standard, unambiguous, language- and regional settings-neutral format. If you must use a string, pass in the following format (which I know you can achieve in .NET with .ToString()):

YYYY-MM-DDTHH:MM:SS

For example:

Console.WriteLine(DateTime.UtcNow.ToString("yyyy-MM-ddTHH:mm:ss"));

The reason this is preferable is that it won't matter where your code runs, what changes are made to the underlying operating system, or what changes are made on the SQL Server side, the date will always be correct and will not be misinterpreted. With the above format, you will never have to worry if it is misinterpreted for any of the wide variety of reasons this could happen (session settings like LANGUAGE and DATEFORMAT, regional settings, locale, even your own code can make assumptions about the incoming string format). There are only a couple of truly safe formats for SQL Server, and mm/dd/yyyy and dd/mm/yyyy are absolutely not among them.

Now, I am not sure why you are using a string in the first place. Best practice suggests that you should be passing a datetime parameter from C#, not a string literal. There is no reason to convert it to a string. A lot of folks will let users enter dates manually into a textbox, and just pass what they enter. Don't do this - force them to pick from a calendar control or set of dropdowns, and construct a datetime value from that. If you let users enter dates, you can never be sure if one person's 09/07/2013 is, in fact, another person's 07/09/2013.

All that said, if you just want to enter the current time, it can be much less problematic to just let SQL Server provide the value, since it has this ability too. So for example if you are storing this data in a column for a new row you are inserting, set up the column as:

ModifiedDate DATETIME NOT NULL DEFAULT GETUTCDATE()

Now you don't have to pass this parameter from .NET or even mention the column name in your INSERT statement.

Upvotes: 10

Related Questions