Try this too: string sql = "INSERT INTO ActiveWindow(empId, time, winName) VALUES ('" + userId + "', GETUTCDATE(), '" + name + "')"; And also consider parameterized queries. You must try this query to insert Current system date time.
You can use now() with default auto fill and current date and time for this. Later, you can extract the date part using date() function. Let us set the default value with some date.
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();
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..
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With