最近在跟SQLServer数据库进行交互的时候发现一个奇怪的问题,在往数据库里边插入日期型数据的时候,在C#里面赋值的为 2014/05/19 23:59:59,但是存到数据库里边就变成了2014/05/20 00:00:00。

问题场景

当时需求是这样的,产品的销售策略要求管理员输入一个产品销售的开始日期SalesStart和结束日期SalesEnd,然后业务会根据当前的时间判断是否在这个产品销售范围内,如果不在则显示未开始或者已过期,所以存储的时候,对SalesEnd进行了处理,在存到数据库的时候,保存的是当天的23:59:59,当时我的处理是这样的:在截止日期加1天然后减去以1毫秒,代码如下:

ProductSalesPolicyModel productSaleModel;
productSaleModel = new ProductSalesPolicyModel();

productSaleModel.SalesStart = item.SaleStartTime;
productSaleModel.ValidStart = item.ValidStartTime;
productSaleModel.SalesEnd = item.SaleEndTime.AddDays(1).AddMilliseconds(-1);
productSaleModel.ValidEnd = item.ValidEndTime.AddDays(1).AddMilliseconds(-1);

然后利用企业库往数据库中使用某个存储过程插入数据,参数赋值的代码如下:

DataParameterCollection parameters;
parameters = new DataParameterCollection();
parameters.Add("SalesStart", DbType.DateTime, salePolicyModel.SalesStart);
parameters.Add("ValidStart", DbType.DateTime, salePolicyModel.ValidStart);
parameters.Add("ValidEnd", DbType.DateTime, salePolicyModel.ValidEnd);
parameters.Add("SalesEnd", DbType.DateTime, salePolicyModel.SalesEnd);

执行插入操作的细节就不写了。

执行完成之后,结果如下:

C# time

 

C# sql time 2

 

在数据库中ProductSalePolicy的表结构如下,其中ValidEnd的类型也是datetime

SalePolicy table design

原因分析

初步分析,有可能是C#中DateTime和SQL Server中datetime数据精度不一致导致的

SQL Server中的时间精度

1. SQL Server中datetime精度

于是到MSDN上去找Transact-SQL中的datetime类型,果不其然,SQL中datetime字段的取值范围为 :

  • 日期范围为:1753.1.1 ~ 9999.12.31
  • 时间范围为:00:00:00 ~23:59:59.997

可见,datetime类型的时间精度是3.33毫秒,如果超过该精度会进行近似到0.000,0.003,0.007 秒,比如:

输入值

SQL 存储值

01/01/98 23:59:59.999

1998-01-02 00:00:00.000

01/01/98 23:59:59.995

01/01/98 23:59:59.996

01/01/98 23:59:59.997

01/01/98 23:59:59.998

1998-01-01 23:59:59.997

01/01/98 23:59:59.992

01/01/98 23:59:59.993

01/01/98 23:59:59.994

1998-01-01 23:59:59.993

01/01/98 23:59:59.990

01/01/98 23:59:59.991

1998-01-01 23:59:59.990

2. SQL Server中datetime2精度

鉴于SQL Server的datetime精度只有3.33毫秒,从SQL Server2008开始,提供了datetime2类型, 该类型的时间精度为00:00:00 ~ 23:59:59.9999999 即100纳秒,10-4毫秒。

System.Data这个主要和外部数据进行交互的名字空间中的DbType枚举可以看到,它提供了DataTime,和DataTime2这两种类型,分别对应了SQL Server中的这两种时间精度,从注释中可以看出,DataTime2的精度是100纳秒

namespace System.Data
{
    // Summary:
    //     Specifies the data type of a field, a property, or a Parameter object of
    //     a .NET Framework data provider.
    public enum DbType
    {
        //
        // Summary:
        //     A type representing a date and time value.
        DateTime = 6,
        //
        // Summary:
        //     Date and time data. Date value range is from January 1,1 AD through December
        //     31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an
        //     accuracy of 100 nanoseconds.
        DateTime2 = 26,
    }
}

C#中Datetime精度

现在再看看C#中的DataTime精度,因为我们在给存储过程中的ValidEnd参数进行赋值的时候定义的类型为DbType.Datetime ,同样,在MSDN上,有对其精度的说明:

Time values are measured in 100-nanosecond units called ticks

100ns就是DateTime的精度,即10-4毫秒。10-4 毫秒也是为DateTime类型的精度

通常我们看到的C#中的DateTime类型精确到100纳秒,即最大时间为23:59:59.9999999。这后面的也就是我们常用到的tick的精度。

原因

因为我在C#代码中处理的时候是item.SaleEndTime.AddDays(1).AddMilliseconds(-1);所以时间精度为0.0001毫秒,为2014/12/16 23:59:59.9990000,然而数据库中字段使用的是datetime类型,该类型的精度为3.33毫秒,所以存到SQL Server的时候被round到了2014/12/17 00:00:000000,从前面的分析也可以看出这个问题。

Time of the day

解决方法

根据分析,有两种方式:

  • 一种是将数据库中的该类型从datetime改为datetime2类型,但是这个类型只有SQL Server 2008及以上版本才支持,他的精度和C# 里面的DateTime数据类型的精度是一致的。
  • 再一种就是修改我们传到SQL Server中的值了,在一些不需要那么高的精度的场景下,比如本文中对结束日期添加23:59:59的这一处理,当时想都没想直接是AddMilliseconds,减的是1ms,而数据库中的精度是3.3ms,所以导致被SQL Server Round到了第二天的零点。这里把 AddMilliseconds改成AddSecond(-1)减1s即可。

总结

C# 中的DateTime的精度和SQL Server中的datetime2的精度(SQL Server 2008及以上版本才有) 是一致的,均为100纳秒(10-4毫秒),而SQL Server中的datetime类型精度只有3.33毫秒,所以如果把C# 中的DateTime类型存储到SQL Server中的datetime中时,会出现精度丢失的情况,在对精度要求较高的系统中,需要注意。