C# SqlDataAdapter DateTime Format datatable invalid json \Date()

Question

[Solved] So it was a stupid issue and i figured it by tracking the variable changes throughout the call stack and fixed it as follows: (Reason and solution mentioned in step 5)

ref: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

1- Database column:

2- Database stored procedure:

3- C# call that stored procedure:

4- C# parse the date (it is returned as a string )

5- Casting that string to datetime (My issue started here, I should not convert the datetime-string back to a datetime object when i need to pass it to a Json() to use it for DataTable plugin for example. Only Razor can handle DateTime objects ..., So the solution was to just skip this step, if you are using a model-model or a view-model, you need to include a string property to store the string version of the datetime or just use another currently-not-in-use property, I used IBAN property to store the datetime-string-version instated of creating a new model or modifying the domain model i was using):

Thanks

If you're using your database properly, this is a non-issue. The column in the database will already be an SQL `DateTime` type, and ADO.Net will automatically give you a .Net `DateTime` type in the results. Anytime you're dealing with converting string values to or from dates with SQL, you're already doing something _**very**_ wrong. — Joel Coehoorn, Sep 30 '18 at 19:23
So your value is stored as a `DateTime` in the database, and your stored procedure converts it to a string? **Don't do that**. — Roger Lipscombe, Oct 01 '18 at 13:07
As a general rule: You should leave values as the correct type until the last possible moment (display to the user). Otherwise you end up on Stack Overflow asking questions like this one. — Roger Lipscombe, Oct 20 '18 at 10:06

GSerg · Answer 1 · 2018-09-30T19:03:30.503

0

On server side,

convert(datetime, 'Dec 30 2006 12:38AM', 109)

On client side,

DateTime.ParseExact("Dec 30 2006 12:38AM", "MMM dd yyyy h:mmtt", System.Globalization.CultureInfo.InvariantCulture)

edited Sep 30 '18 at 19:03

answered Sep 30 '18 at 18:58

GSerg

76,472
17
159
346

It needs another h: "MMM dd yyyy h:mmtt" Thanks – Adel Mourad Oct 01 '18 at 10:05
1

@AdelMourad It may have another, `h`, but it does not need it. With `h`, it will handle both `12:38AM` and `2:38AM`. With `hh`, it will fail on `2:38AM`. – GSerg Oct 01 '18 at 11:00

score 0 · Answer 2 · answered Sep 30 '18 at 19:03

Why don't you use DateTime.Parse()?

using System;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            var dateString = "Dec 30 2006 12:38AM";
            var date = DateTime.Parse(dateString);
            Console.WriteLine(date);
            // results is 30/12/2006 00:38:00 (according to my DateTime format settings)
            Console.ReadLine();
        }
    }
}

C# SqlDataAdapter DateTime Format datatable invalid json \Date()

2 Answers2