1

I'm using mongodb C# driver 2.7.3 to access my mongodb server. In my table I have a field created_at which is in ISODate format (e.g 2017-03-20T16:47:07.911Z). When I tried to convert this field into C# datetime it gives the following error

System.FormatException: 'String was not recognized as a valid DateTime.'

I want my datetime to be in this format 'yyyy-mm-dd hh:mm:ss.fffffff'

Here is my code 

        string connectionString = "localhost:27017";
        string databaseName = "test";

        var client = new MongoClient(connectionString);
        var database = client.GetDatabase(databaseName);
        var collection = database.GetCollection<BsonDocument>("table");
        var documents = collection.Find(new BsonDocument()).Limit(10).ToList();
        string format = "yyyy-MM-dd HH\\:mm\\:ss.fffffff";

        foreach (BsonDocument document in documents)
        {
            var date = (document["created_at"] == null ? "" : document["created_at"].ToString());
            var created_at = DateTime.ParseExact(date.ToString(), format, CultureInfo.InvariantCulture);
            Console.WriteLine("date : "+date.ToString());
        }
        Console.ReadLine();

What am I doing wrong here? How can we format this date into a format I want?

Muhammad Hammad Ejaz
  • 51
  • 1
  • 9
  • Try doing [.ToString("o")](https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#the-round-trip-o-o-format-specifier) ? – Kunal Mukherjee Apr 17 '19 at 10:18
  • `.AsDateTime` instead of `.ToString()`. Also, DateTime variables [have no format](https://stackoverflow.com/questions/35650681/how-do-i-format-a-datetime-in-a-different-format). – CodeCaster Apr 17 '19 at 10:18
  • 1
    If you see `ISODate()` in something like the `mongo` shell, it's actually a BSON Date which is actually already read by the .NET driver into a `DateTime` object by default. It's not a string at all and you should not need to do anything to it. – Neil Lunn Apr 17 '19 at 10:19
  • @KunalMukherjee I did and got the error Error CS1503 Argument 1: cannot convert from 'string' to 'System.IFormatProvider' – Muhammad Hammad Ejaz Apr 17 '19 at 10:24
  • @NeilLunn but i want my datetime to be in this format "yyyy- MM- dd HH\\:mm\\:ss.fffffff" .How can we do this? – Muhammad Hammad Ejaz Apr 17 '19 at 10:27
  • `foreach (BsonDocument document in documents) { var created_at = document["created_at"].ToString("o"); Console.WriteLine("Created at : " + created_at); }` Try doing this – Kunal Mukherjee Apr 17 '19 at 10:29
  • 1
    You really don't want the date any any other format than a BSON Date in the database. If you want to format a `DateTime` as a "string" for output purposes, then that's a different question. Don't mess with the MongoDB date. It's actually an internal integer of milliseconds since epoch, and that's a lot less storage than the "string". – Neil Lunn Apr 17 '19 at 10:34
  • Possible duplicate of [C# with mongodb DateTime Convert](https://stackoverflow.com/questions/26914031/c-sharp-with-mongodb-datetime-convert) – smolchanovsky Apr 17 '19 at 10:38
  • @NeilLunn I want to store this date in sql server where I have a column of type datetime2 – Muhammad Hammad Ejaz Apr 17 '19 at 10:39
  • @KunalMukherjee it gives the output in this format yyyy-mm-ddThh:mm:ss.fffffffZ. I dont want "T" and "Z". Instead of T and Z i want my output to look like this 'yyyy-mm-dd hh:mm:ss.fffffff' – Muhammad Hammad Ejaz Apr 17 '19 at 10:50
  • minus the timezone ? – Kunal Mukherjee Apr 17 '19 at 10:51
  • `foreach (BsonDocument document in documents) { var date = document["created_at"]; var created_at = date.ToString("yyyy-mm-dd hh:mm:ss.fffffff"); Console.WriteLine("created_at : " + created_at ); }` try this ? – Kunal Mukherjee Apr 17 '19 at 11:02

0 Answers0