SQL Server 中的处理日期
我正在使用 asp.net 开发一个网站.我从网页获取日期，然后根据用户输入我想从 SQL Server 数据库(使用存储过程)获取结果.
I am working on a website in asp.net. I am getting a date from a web page and then depending on the user input I want to get results from SQL Server database (using stored procedures).
问题是我只能从 UI 中获取这种格式的日期 2016-10-08 ，它是字符串类型.但是在数据库中，我有一个 datetime 类型的列，格式为 2016-10-08 17:38:00.000.
Problem is that I am getting date only from UI in this format 2016-10-08 which is of type string. But in the database, I have a column which is of type datetime in this format 2016-10-08 17:38:00.000.
我正在使用此查询进行搜索，但它不起作用.
I am using this query to search but it does not work.
select *
from table
where acceptedDate like @sDate+ '%';
其中 sDate 是存储过程的输入参数.请帮忙.谢谢
where sDate is input parameter for stored procedure. Please help. thanks
推荐答案
不要将日期作为字符串传递.将它们作为 DateTime 传递.
.Net DateTime 直接映射 到 SQL Server 的 DateTime.您所要做的就是将字符串解析为 .Net 代码中的 DateTime 结构，并将其作为参数传递给您的存储过程.要搜索特定日期并忽略 DateTime 的时间部分，最好在您的 sql 中使用 >= 和 < :
Don't pass dates as strings. Pass them as DateTime.
The .Net DateTime maps directly to SQL Server's DateTime. All you have to do is parse the string to a DateTime struct in your .Net code and pass it as a parameter to your stored procedure.
To search for a specific date and ignore the Time portion of the DateTime, better use >= and < in your sql:
select *
from table
where acceptedDate >= @Date
AND acceptedDate < DATEADD(DAY, 1, @Date);
相关文章