CODEWITHSUNDEEP
c#sqlsql-server
user10997800
user10997800

Reputation:

Mismatches on DateTime between C# and SQL server

I create DateTime in C# like this

DateTime oDate = Convert.ToDateTime(xate);

that returns 22/09/2020 01:27:00 ب.ظ}

and save it in SQL server after saving I see the time that stored is Weird like this

enter image description here

and when I try to run a SQL command like this

INSERT INTO HomeVisit (LifeplusCaseId, FromDateTime, ToDateTime)
VALUES ('39909F03-2DEF-4682-89CA-0000DCC3E098','22/09/2020 12:00:00 ق.ظ', '22/09/2020 12:00:00 ق.ظ');

I get this error

Conversion failed when converting date and/or time from character string.

I figure out the time that generates in C# is vice versa the format in the SQL Server.

I don't know how to save DateTime in C# to SQL Server.

My code for saving datatime in SQL Server is:

HomeVisitRow homeVisit = BehzistiDb2.List<HomeVisitRow>().Where(x => x.LifeplusCaseId == lifeplusCaseId && x.FromDateTime <= oDate && x.ToDateTime > oDate).FirstOrDefault();

if (homeVisit == null)
{
    homeVisit = new HomeVisitRow
    {
        Id = Guid.NewGuid(),
        LifeplusCaseId = lifeplusCaseId,
        FromDateTime = oDate,
        ToDateTime = oDate.AddMonths(6) 
    };

    BehzistiDb2.Insert<HomeVisitRow>(homeVisit);

Upvotes: 1

Views: 326

Answers (2)

Manish jha
Manish jha

Reputation: 13

You format the date according to your requirement just see the below examples.

DateTime oDate = Convert.ToDateTime(xate); 
oDate = oDate.ToString("yyyy-MM-dd");

Upvotes: 0

IndieGameDev
IndieGameDev

Reputation: 2974

Conversion failed when converting date and/or time from character string

The problem is that you insert a string ' ' into your SQL Database instead of a DateTime Object like you specified.

We can create a DateTime Object from a string easily if we use DateTime.TryParseExact()

Example:

DateTime fromDate;
string txtStartDate = "22/09/2020 01:27:00";
DateTime.TryParseExact(txtStartDate, "dd/MM/yyyy HH:mm:ss", 
    System.Globalization.CultureInfo.InvariantCulture,
    System.Globalization.DateTimeStyles.None, out fromDate);

Console.WriteLine(fromDate.ToString());

Now we can also insert that Variable into our SQL Statement as it's value.

INTO HomeVisit (LifeplusCaseId, FromDateTime, ToDateTime)
VALUES ('39909F03-2DEF-4682-89CA-0000DCC3E098', fromDate, toDate);

Upvotes: 2

Related Questions