在 SQL Server 2008 中的日期和时间之间搜索 [英] Search between dates and times in SQL Server 2008
问题描述
我需要在日期和时间之间进行搜索.
例如,在日期:30/02/2007,时间:10:32
和日期:21/06/2008,时间:14:19
之间</code>>
最简单的查询是什么?
提前致谢.
您应该查看 SQL Server 中可用的日期时间格式:http://msdn.microsoft.com/en-us/library/ms187928.aspx
yyyy-mm-dd hh:mi
是你应该使用的:
试试:
SELECT*从记录WHERE DateCreated>='2007-02-30 10:32' AND DateCreated<='2008-06-21 14:19'
在上述查询中,如果 DateCreated
是日期时间列,则字符串将转换为日期时间数据类型.并且查询将起作用.
您可以创建日期时间数据类型的局部变量并使用如下查询:
DECLARE @StartDate 日期时间,@EndDate 日期时间选择@StartDate='2007-02-30 10:32',@EndDate='2008-06-21 14:19'选择*从记录WHERE DateCreated>=@StartDate AND DateCreated<=@EndDate
我喜欢使用 <、<=、>= 或 >,因为它比 BETWEEN
具有更大的灵活性,并迫使您考虑是否包含端点.
要考虑的另一件事是获取一整天的所有数据:
DECLARE @StartDate 日期时间,@EndDate 日期时间--设置你想要的日子选择@StartDate='2007-02-30 10:32',@EndDate='2008-06-21 14:19'--去掉时间SELECT @StartDate=DATEADD(day,DATEDIFF(day,0,@StartDate),0), @EndDate=DATEADD(day,DATEDIFF(day,0,@EndDate),0)--获取2007-02-30"上的所有内容,直到2008-06-21"当天结束选择*从记录WHERE DateCreated>=@StartDate AND DateCreated<@EndDate+1
I need to search between dates and times.
For example, between date: 30/02/2007, time: 10:32
and date: 21/06/2008, time: 14:19
What is the most simple query for this?
Thanks in advance.
you should look at the date time formats available in SQL Server: http://msdn.microsoft.com/en-us/library/ms187928.aspx
yyyy-mm-dd hh:mi
is what you should use:
try:
SELECT
*
FROM Records
WHERE DateCreated>='2007-02-30 10:32' AND DateCreated<='2008-06-21 14:19'
in the above query the strings will be converted to datetime data type if DateCreated
is a datetime column. and the query will work.
you can create local variables of datetime data type and use a query like:
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='2007-02-30 10:32', @EndDate='2008-06-21 14:19'
SELECT
*
FROM Records
WHERE DateCreated>=@StartDate AND DateCreated<=@EndDate
I like using <, <=, >=, or > because it allows more flexibility than BETWEEN
and forces you to think about including endpoints or not.
Another thing to consider is getting all data from a complete day:
DECLARE @StartDate datetime, @EndDate datetime
--set the days you want
SELECT @StartDate='2007-02-30 10:32', @EndDate='2008-06-21 14:19'
--remove the time
SELECT @StartDate=DATEADD(day,DATEDIFF(day,0,@StartDate),0), @EndDate=DATEADD(day,DATEDIFF(day,0,@EndDate),0)
--get everything on '2007-02-30' up to the end of the day on '2008-06-21'
SELECT
*
FROM Records
WHERE DateCreated>=@StartDate AND DateCreated<@EndDate+1
这篇关于在 SQL Server 2008 中的日期和时间之间搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!