在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屋!