CODEWITHSUNDEEP
c#sqldatetime
sama
sama

Reputation:

insert datetime value in sql database with c#

How do I insert a datetime value into a SQL database table where the type of the column is datetime?

Upvotes: 45

Views: 249716

Answers (8)

AvahW
AvahW

Reputation: 2201

I got this error from a stupid mistake - remember not to put the parameter in invreted commas, so use MY_DATE_FIELD=@mydate NOT MY_DATE_FIELD='@mydate'

Upvotes: 0

joonas
joonas

Reputation: 135

This is an older question with a proper answer (please use parameterized queries) which I'd like to extend with some timezone discussion. For my current project I was interested in how do the datetime columns handle timezones and this question is the one I found.

Turns out, they do not, at all.

datetime column stores the given DateTime as is, without any conversion. It does not matter if the given datetime is UTC or local.

You can see for yourself:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT * FROM (VALUES (@a, @b, @c)) example(a, b, c);";

        var local = DateTime.Now;
        var utc = local.ToUniversalTime();

        command.Parameters.AddWithValue("@a", utc);
        command.Parameters.AddWithValue("@b", local);
        command.Parameters.AddWithValue("@c", utc.ToLocalTime());

        using (var reader = command.ExecuteReader())
        {
            reader.Read();

            var localRendered = local.ToString("o");

            Console.WriteLine($"a = {utc.ToString("o").PadRight(localRendered.Length, ' ')} read = {reader.GetDateTime(0):o}, {reader.GetDateTime(0).Kind}");
            Console.WriteLine($"b = {local:o} read = {reader.GetDateTime(1):o}, {reader.GetDateTime(1).Kind}");
            Console.WriteLine($"{"".PadRight(localRendered.Length + 4, ' ')} read = {reader.GetDateTime(2):o}, {reader.GetDateTime(2).Kind}");
        }
    }
}

What this will print will of course depend on your time zone but most importantly the read values will all have Kind = Unspecified. The first and second output line will be different by your timezone offset. Second and third will be the same. Using the "o" format string (roundtrip) will not show any timezone specifiers for the read values.

Example output from GMT+02:00:

a = 2018-11-20T10:17:56.8710881Z      read = 2018-11-20T10:17:56.8700000, Unspecified
b = 2018-11-20T12:17:56.8710881+02:00 read = 2018-11-20T12:17:56.8700000, Unspecified
                                      read = 2018-11-20T12:17:56.8700000, Unspecified

Also note of how the data gets truncated (or rounded) to what seems like 10ms.

Upvotes: 2

PedPak
PedPak

Reputation: 177

you can send your DateTime value into SQL as a String with its special format. this format is "yyyy-MM-dd HH:mm:ss"

Example: CurrentTime is a variable as datetime Type in SQL. And dt is a DateTime variable in .Net.

DateTime dt=DateTime.Now;
string sql = "insert into Users (CurrentTime) values (‘{0}’)";

sql = string.Format(sql, dt.ToString("yyyy-MM-dd HH:mm:ss") );

Upvotes: -1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56727

The following should work and is my recommendation (parameterized query):

DateTime dateTimeVariable = //some DateTime value, e.g. DateTime.Now;
SqlCommand cmd = new SqlCommand("INSERT INTO <table> (<column>) VALUES (@value)", connection);
cmd.Parameters.AddWithValue("@value", dateTimeVariable);

cmd.ExecuteNonQuery();

Upvotes: 100

Reshma
Reshma

Reputation: 904

 DateTime time = DateTime.Now;              // Use current time
 string format = "yyyy-MM-dd HH:mm:ss";    // modify the format depending upon input required in the column in database 
 string insert = @" insert into Table(DateTime Column) values ('" + time.ToString(format) + "')";

and execute the query. DateTime.Now is to insert current Datetime..

Upvotes: 27

Joel
Joel

Reputation: 19368

using (SqlConnection conn = new SqlConnection())
using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = "INSERT INTO <table> (<date_column>) VALUES ('2010-01-01 12:00')";
    cmd.ExecuteNonQuery();
}

It's been awhile since I wrote this stuff, so this may not be perfect. but the general idea is there.

WARNING: this is unsanitized. You should use parameters to avoid injection attacks.

EDIT: Since Jon insists.

Upvotes: 1

Andrea
Andrea

Reputation: 2077

It's more standard to use the format yyyy-mm-dd hh:mm:ss (IE: 2009-06-23 19:30:20)

Using that you won't have to worry about the format of the date (MM/DD/YYYY or DD/MM/YYYY). It will work with all of them.

Upvotes: 8

atfergs
atfergs

Reputation: 1684

INSERT INTO <table> (<date_column>) VALUES ('1/1/2010 12:00')

Upvotes: -10

Related Questions