CODEWITHSUNDEEP
c#sqldatabasedate
Nave Tseva
Nave Tseva

Reputation: 878

Inserting a date into a database does not work (ASPX/C#)

I have this C# code:

string RegisterDate = DateTime.Now.ToString();
RegisterDate = RegisterDate.Remove(10);
RegisterDate = RegisterDate.Replace('/', '-');
RegisterDate = String.Join("-", RegisterDate.Split('-').Reverse());

Which gives thie result: 01-06-2013

The problem is that when I try to insert it to the table I get this result: 21/06/1894

When I get the date via input it works great in the same date format, so why in this case it doesn't work?

update

If I try this:

    var RegisterDate = DateTime.Today.Date;

I get Error :

Syntax error (missing operator) in query expression

Wish for help, thanks!

Upvotes: 0

Views: 688

Answers (2)

Bhushan Firake
Bhushan Firake

Reputation: 9458

Try

string RegisterDate = DateTime.Now.ToString("M-d-yyyy");

and then store in database.

There is no need to manually convert date to different representation. You can go through this Custom Date and Time Format Strings. But, I agree on Jon Skeet's comment below this answer:

If you want to represent a date/time type, use a date/time type. That way you're able to take advantage of all kinds of things that the database can do with date/time values, and you'll never get any non-date/time values in that field.

Note:

DateTime type uses the Gregorian calendar as their default calendar. So, as pointed out by Jon Skeet, this answer won't work with other calenders(Non-Gregorian calendars).

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1503419

Don't use a string conversion at all. Assuming your data type in the database is DateTime or something similar, just use a parameter and specify its value as the DateTime in your C# code to start with. (I'm assuming you're already using parameterized SQL rather than embedding data straight in your SQL. If you're not using parameters yet, start right away!)

I'd suggest using DateTime.Today to make it clearer that you're only interested in the date part. (Note that this means that the same code running in different places could end up inserting different dates - is that okay? Normally I don't like letting the system local time zone affect things.)

You should generally avoid string conversions unless you really need a string representation of the data. At other times they just cause trouble.

EDIT: You asked for an example. It would be something like:

using (var connection = new SqlConnection(...))
{
    connection.Open();
    using (var command = new SqlCommand(
        "INSERT INTO Foo (Name, RegisterDate) VALUES (@Name, @RegisterDate)",
        connection))
    {
        command.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar))
                          .Value = name;
        // TODO: Consider whether you really want the *local* date, or some
        // fixed time zone such as UTC
        command.Parameters.Add(new SqlParameter("@RegisterDate", SqlDbType.DateTime))
                          .Value = DateTime.Today;
        command.ExecuteNonQuery();
    }
}

Upvotes: 3

Related Questions