SQL Server 中的处理日期 [英] Handling date in 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);

这篇关于SQL Server 中的处理日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！