question about DATETIME2 string format

Question

Hello, in my c # code I am using the DateTime transformation in a string with the following format "yyyy-MM-dd HH:mm:ss.fff" and then I use this to apply in the expression "CAST ('yyyy-MM-dd HH:mm:ss.fff ' AS DATETIME2) ". Can I be sure that this format will be successfully converted to DATETIME2 regardless of the settings of the SQL Server and its environment? from the content in https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15 one can come to such conclusions, but I'm interested to know are there settings or parameters that can change this?

Answer 1

SQL Server can convert ISO 8601 datetime strings properly with an explict or implicit conversion since those formats are independent of regional settings. You can similarly use DateTime.ParseExact(yourDateTimeString, "yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture) in the c# code and pass the value natively as a datetime2 parameter, avoiding the need for SQL Server to parse the string.

Answer 2

If you know the expected format of the datetime string, you should always use CONVERT with the appropriate style, instead of forcing SQL Server go guess the format.

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#date-and-time-styles

Answer 3

I will have to admit that I think that you are asking the wrong question. I mean, why would you care about this in the first place? You should pass the datetime value as a datetime value are a parameter. When you talk about strings, I get the feeling that you are inlining parameter values into the string - a very bad thing to do from several perspectives. (And your question is a token of one of them.)

Answer 4

Hi @Pugachuk, Sergey ,

Welcome to the Microsoft Q&A Forum!

In fact, the official Microsoft documents you refer to have listed all the settings that can be converted. In addition, in SQL Server you can use cast and convert.Tom has provided a link to introduce them.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.