Datetime Format to SQL Format Using C#

DateTime format to SQL format using C#

try this below

DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");

C-Sharp DateTime Culture format for SQL Date-Format

Does it matter if there is a culture which ensures that DateTime.ToString will output this format? What matters is if you want to output a DateTime in a culturally appropriate format or not. If you don't care about a (given) culture, why do you ask for it?

If all you need is a DateTime as a string in that format, use:

string dtAsString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

SQL datetime to C# string and back to SQL datetime

Parse it into a dateTime again

DateTime myTime = DateTime.Parse(myString);

and back into a proper to string

myTime.ToString("yyyy-MM-dd HH:mm:ss");

Or just read it into a datetime and cut out the middleman.

Convert Datetime format in SQL Server or C#

Assuming datetime is your date time variable:

dateTime.ToString("yyyy-MM-ddTH:mm:sszzz");

You will get a warning saying that this is not the recommended way to persist timezone information, however.

How to map formatted Datetime value in C#

There is no such thing as a "formatted datetime". If you format a DateTime value, it isn't a DateTime anymore; it's just a string. Typically, you should return a plain (no format) DateTime value back from the database into the model, and let the view handle any formatting needs. This is true even for Web API scenarios, where the View might just be JSON.

So in the question, you see the bad value because the Model is expecting a DateTime, but we supplied a string, and it failed to parse it. We can fix this by removing the FORMAT call from the SQL, and letting it return an actual DateTime, like this:

string Sql = "SELECT StatusId, DS.CreatedAt, DS.CreatedBy, S.[Text] " +
              " FROM[Inspection].[Dossier_Status] DS " +
              "INNER JOIN [DT_Inspection].[Status] S ON S.Id = StatusId " +
              "WHERE DossierId = @Dossier_ID " +
              "ORDER BY DS.CreatedAt DESC";

using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
await using var sqlConnection = new SqlConnection(configuration.GetConnectionString("DefaultConnection"));

var result = await sqlConnection.QueryAsync<Dossier_StatusVUE>(Sql, new {Dossier_ID = DossierID});

Now the correct value should get into the result variable, and we fixed the nasty sql injection issue at the same time.

However, this is only half the fix. The next step is updating the view to know how to present this DateTime value. However, we don't have enough information on how the View is constructed, so I'll have to leave that part to you.

SQL server DateTime and C# DateTime

The DateTime, which is represented by the string, isn't supported by the calender.

This error is being given because your C# application views the date 2012-14-10 as saying the 14th month, 10th day, and 2012th year. The day and year work find, but the month doesn't. Further, don't try and change how your C# application views the date, that's based off the culture of the system.

You're confusing how to define a DateTime object and how to display one.

Since you're storing your date as a DateTime in SQL, there's not a good reason for me to believe that you would need to do any kind of parsing. Consider the below code sample.

var dataTable = new DataTable();
var dataAdapter = new SqlDataAdapter("SELECT * FROM YourTable", "{connection string}");

dataAdapter.Fill(dataTable);

var yourDate = dataTable.Rows[0]["Date"]; <=== the type will be DateTime, simple.

Adding Parameters

Let's take your example query:

"SELECT * FROM date_test WHERE id = '4' AND date BETWEEN '" + dt1 + "' AND '" + dt2 + "'";

And let's fix it a bit, consider the below example:

var dataTable = new DataTable();
var dataAdapter = new SqlDataAdapter("SELECT * FROM date_test WHERE id = @ID AND date BETWEEN @StartDate AND @EndDate", "{connection string}");

dataAdapter.SelectCommand.Parameters.AddWithValue("@ID", "4");
dataAdapter.SelectCommand.Parameters.AddWithValue("@StartDate", new DateTime(2012, 10, 1));
dataAdapter.SelectCommand.Parameters.AddWithValue("@EndDate", new DateTime(2012, 10, 14));

dataAdapter.Fill(dataTable);

var yourDate = dataTable.Rows[0]["Date"]; <=== the type will be DateTime, simple.

---

Related Topics