1

Problem: I got SQL database with column of type datetime and I am trying to get the value to my c# API variable of type DateTime. 

datetime format in SQL DB:  2011-11-14 00:00:00.000  // So that equals:
                            yyyy - MM - dd HH:mm:ss.ttt

Here are some of my attempts to get right value: 

MyClass.MyProperty= Convert.ToDateTime(reader.GetSqlDateTime(0));
MyClass.MyProperty= Convert.ToDateTime(reader.GetDateTime(0));
MyClass.MyProperty= Convert.ToDateTime(reader.GetValue(0));
MyClass.MyProperty= DateTime.ParseExact(reader.GetValue(0).ToString(), "yyyy - MM - dd HH:mm:ss.ttt", CultureInfo.InvariantCulture);

Every time that my attempt compiles without error I get following output: 

0001-01-01T00:00:00

I also tried in combination with DateTimeOffset and getting value from DB in string and then converting it to Datetime, but VS

Update:

I tried executing my SQL statement on database via server and found out that UserGuid of my profile didn't have any time in selected column. (I used different user credentials when I initially tested SQL statement) I fixed couple of things and debugged a bit more and now connection and reader work as they should. I still have problem with formatting tho.

DB value: 2014-08-07 00:00:00.000
Output: 8/7/2014 12:00:00 AM

I tried telling it how to format DateTime format with ParseExact, but it throws this exception:

System.FormatException' in mscorlib.dll

Additional information: String was not recognized as a valid DateTime.

Here is my code for entire reader: 

        while (reader1.Read())
        {
            newUsagePlan.PlanValidityFrom = DateTime.ParseExact(Convert.ToString(reader1.GetValue(0)), "yyyy - MM - dd HH:mm:ss.ttt", CultureInfo.InvariantCulture);
            string lol1 = reader1.GetValue(0).ToString(); // Output: "8/7/2014 12:00:00 AM"
            string lol2 = reader1.GetSqlDateTime(0).ToString(); // Output: "8/7/2014 12:00:00 AM"
        }

FINAL CONCLUSION:

I solved this problem easily now, by simply using this: 

string sqlFormattedDate = reader1.GetDateTime(0).ToString("yyyy-MM-dd HH:mm:ss.fff");

And got desired output: 2014-08-07 00:00:00.000

One piece of advice: Try not to work on a production and development database in a single project, it can blow your brains out when there are inconsistencies in database data and you are not aware of it. Cheers :)

halfer
  • 19,824
  • 17
  • 99
  • 186
MultiHunter
  • 123
  • 2
  • 10
  • What outputs are you getting? is your SQL in the same timezone you are? – BugFinder Jan 04 '17 at 14:21
  • i think your question is a duplicate of http://stackoverflow.com/questions/17418258/datetime-format-to-sql-format-using-c-sharp – srinced Jan 04 '17 at 14:23
  • https://msdn.microsoft.com/en-us/library/xhz1w05e(v=vs.110).aspx – vipersassassin Jan 04 '17 at 14:23
  • Yes, my DB is in same timezone as me. And I always get the same result: 0001-01-01T00:00:00. I tried with different SELECT statements, to get different dates, but that didnt help either. If it helps, my DB Collation is: SQL_Latin1_General_CP1_CI_AS @BugFinder – MultiHunter Jan 04 '17 at 14:24
  • 1
    Can you put more code in around your reader? so we can see the select etc you're calling, it maybe you're getting a null back - what happens if you run the same query direct in SQL? – BugFinder Jan 04 '17 at 14:33
  • 1
    if `reader.GetSqlDateTime(0)` returns a string, then this can convert to a `DateTime` without issue. Your returning value is likely the issue, can you debug and look at the value there? – vipersassassin Jan 04 '17 at 14:37
  • Which dbms are you using? – jarlh Jan 04 '17 at 14:37
  • I am using MS SQL Server, ISS for hosting and MS SQL SMS for developement. – MultiHunter Jan 04 '17 at 15:40
  • I updated my question accordingly after investigating deeper based on your suggestions. @BugFinder – MultiHunter Jan 04 '17 at 15:46

0 Answers0