C# and SQL server datetime format

Question

I have the following C# method to update a value in a SQL server:

public void Save()
{
    int TotalFail = TotalRecords - SuccessCount;

    DataAccess_MSSQLServer oDAM = new DataAccess_MSSQLServer();

    try
    {                                                                                                                                                                                                                                           
        oDAM.Update("Update TBL_FulfilmentBatch SET ReturnDate = " + oDAM.GetFieldValueSQL(processDate) + ", TotalSuccess = " + oDAM.GetFieldValueSQL(SuccessCount) + ", TotalFail = " + oDAM.GetFieldValueSQL(TotalFail) + ", ProcessedDate = " + oDAM.GetFieldValueSQL(DateTime.Now.ToString("yyyyMMddHHmmss")) + " WHERE FulfilmentHouseID = (SELECT FulfilmentHouseID FROM TBL_fulfilmentHouse WHERE [Description] Like 'Current') AND BatchNumber = " + oDAM.GetFieldValueSQL(batchNumber));
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace);
    }

}

Where

oDAM.GetFieldValueSQL

is a method used to format strings etc for SQL statements.

The string processDate has the value: 2015-07-23

The field this corresponds to in the database is a datetime field.

I also have:

ProcessedDate = " + oDAM.GetFieldValueSQL(DateTime.Now.ToString("yyyyMMddHHmmss"))

which inserts a C# datetime field in the database.

These 2 datetime fields should now be in the same date format.

The problem is that when inserted into the databse, they come out as follows:

Which is opposite formats.

why is this happening?

They are both being passed in as strings, in the same format.

There is nothing in the table that tells it to convert to a particular datetime format.

Older entries of the ProcessedDate column are in American format.

Does it think this date is in American format aswell?

What can I do?

1. Sql server does not store date display format. 2. Always use parameterized queries. read about sql injection. — Zohar Peled
– Zohar Peled, Commented Oct 8, 2015 at 11:31
Use a parametrized insert and pass a DateTime type or why not use GETDATE() in the SQL for the servers current time? As the values are not quoted yyyy-mm-dd is a subtraction operation ... — Alex K.
– Alex K., Commented Oct 8, 2015 at 11:32
Pass yyyyMMddHHmmssFFF instead of yyyyMMddHHmmss e.g. ProcessedDate = " + oDAM.GetFieldValueSQL(DateTime.Now.ToString("yyyyMMddHHmmssFFF")). Would that help You? — Mukesh Kumar
– Mukesh Kumar, Commented Oct 8, 2015 at 11:34

C.Evenhuis · Accepted Answer · 2015-10-08 11:35:35Z

1

You should use parameterized queries.

If you keep trying, eventually you'll manage to tweak your string into format that "works". For your current database instance, running the client application on your computer. It might break on the 13th of the month (when days are mistaken for months).

The ADO.NET providers can translate a DateTime and other primitive types for you, and prevent SQL injection vulnerabilities at the same time. You will probably even see a performance gain if you reuse the DbCommands.

answered Oct 8, 2015 at 11:35

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# and SQL server datetime format

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related