DateTime.Now in C# to yield Datetime2(3) in SQL Server datatype

Question

I tried several ways to retrieve datetime2(3) equivalent from C# code but in vain.

One of them is as follows.

DateTime dt = DateTime.Now.AddMilliseconds(DateTime.Now.Millisecond);

I need the following format:

YYYY-MM-DD HH:MM:SS.FFF

But from the above code, I got the following result

6/19/2012 11:15:08 PM

When I tried the following way,

 string myTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff");
 DateTime dd = Convert.ToDateTime(myTime);

it is throwing following error

String was not recognized as a valid DateTime.

I need the date in datetime2(3) format only instead you can suggest me to save as nvarchar. But I need to sort the entries according to the datetime2 they were updated.

Is there any other way to solve this?

Answer 1

var format = "yyyy-MM-dd HH:mm:ss:fff";
var stringDate = DateTime.Now.ToString(format);
var convertedBack = DateTime.ParseExact(stringDate, format, CultureInfo.InvariantCulture);

DateTime is a data type representing dates and times and does not store format information. The milliseconds are always stored in DateTime. The only time you need to specify milliseconds is when choosing how to represent the DateTime as another type, like a string.