使用参数在SQL中插入日期时间而无需毫秒 [英] Insert datetime in SQL without millisecond using a parameter

问题描述

我需要在我的SQL Server数据库中插入一个.net DateTime，而不需要毫秒。

I need to insert a .net DateTime in my SQL Server DataBase without the milliseconds.

dtNow = Date.Now
myCmd.Parameters.AddWithValue("MyDate", dtNow)

我想要我的值数据库，例如： 2014-01-15 17：18：04.000

以秒为单位的值更准确。

I want the values on my database to be, for example: 2014-01-15 17:18:04.000
Being the seconds the more accurate value.

但是当我这样插入时，它会增加实际的毫秒数：

But it adds the actual milliseconds when I make an insert like this one:

INSERT Table1 (MyDate, id, MyValue, DateModified) 
VALUES (@ADate, @ID, @Value, @MyDate)

如何以最简单的方式实现这一目标？

How can I achieve this in the simplest way?

推荐答案

同时您可以减去注释中建议的毫秒数，但仍会留下亚毫秒的值。 可能不会引起问题，但是驱动程序有可能会将亚毫秒值四舍五入到整毫秒。 IMO（IMO）更干净，可避免有任何亚秒级的值，因此您插入的值与要存储的值相同。我更喜欢使用：

While you could subtract the number of milliseconds as suggested in comments, that would still leave you with submillisecond values. That may not cause a problem, but it's possible that the driver will round the submillisecond value up to a whole millisecond. It's cleaner (IMO) to avoid having any subsecond value at all, so that the value you insert is the same as the value which gets stored. I'd prefer to use:

var truncated = new DateTime(dtNow.Year, dtNow.Month, dtNow.Day,
                             dtNow.Hour, dtNow.Minute, dtNow.Second);
// Use truncated as the parameter in your command

这样，它显然会仅具有年/月/日/小时/分钟/秒的值。

That way it will clearly only have year/month/day/hour/minute/second values.

如果您定期进行此操作，则可能需要编写扩展名方法，以便您可以使用：

If you find yourself doing this regularly, you might want to write an extension method so that you can use:

var truncated = dtNow.TruncateToSecond();

这篇关于使用参数在SQL中插入日期时间而无需毫秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！