怎么办查询 - 日期时间从.NET在SQL为smalldatetime？ [英] DateTime from .NET to smalldatetime in SQL - how to do queries?

问题描述

我在code进行的DateTime 部分，我想用它在我的SQL Server数据库的查询。

I have a DateTime component in my code, and I want to use it for a query in my SQL Server database.

当插入这个组件，似乎是没有问题的，但在查询 SMALLDATETIME 值的时候，我只是不知道如何做到这一点。该数据集总是空的。

When inserting this component, there seems to be no problem, but when querying for smalldatetime values, I just don't know how to do it. The dataset is always empty.

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ReadDates"; 

dataset = new DataSet();

SqlParameter parameter = new SqlParameter("@date", SqlDbType.SmallDateTime);
parameter.Value = DateTime.Now();
cmd.Parameters.Add(parameter);

dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dataset);
return dataset;

这是我的存储过程：

And this is in my stored procedure:

select * from TableDates
where ValueDate <= @date

所以，我有运行过程中的SQL Server Management Studio中没有问题，这种格式的输入参数时：2000年3月3日4时05分零一秒 ，但是当传递的DateTime ，查询总是空的。有什么建议？

So I have no problems running the procedure in SQL Server Management Studio, when entering a parameter in this format: '2000-03-03 04:05:01', but when passing a DateTime, the query is always empty. Any suggestions?

推荐答案

下面是我的$ C $下创建的SqlParameter的日期时间;对于SQL Server 2008，我们传递的价值为DATETIME2因为SQL将含蓄的DATETIME2，只要它是转换为所有其他日期类型目标类型的范围...

Here's my code for creating the SqlParameter for a Datetime; For SQL Server 2008 we pass the value as datetime2 since SQL will implicity convert from datetime2 to every other date type so long as it is within the range of the target type...

            // Default conversion is now DateTime to datetime2. The ADO.Net default is to use datetime. 
            // This appears to be a safe change as any datetime parameter will accept a datetime2 so long as the value is within the
            // range for a datetime. Hence this code is acceptable for both datetime and datetime2 parameters, whereas datetime is not 
            // (because it doesn't handle the full range of datetime2).
            SqlParameter sqlParam = new SqlParameter(name, SqlDbType.DateTime2);

这篇关于怎么办查询 - 日期时间从.NET在SQL为smalldatetime？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！