我有一个start_date和end_date。我想要得到这两个日期之间的日期列表。有人能帮我指出我的查询中的错误吗?
select Date,TotalAllowance
from Calculation
where EmployeeId=1
and Date between 2011/02/25 and 2011/02/27
这里Date是一个datetime变量。
我有一个start_date和end_date。我想要得到这两个日期之间的日期列表。有人能帮我指出我的查询中的错误吗?
select Date,TotalAllowance
from Calculation
where EmployeeId=1
and Date between 2011/02/25 and 2011/02/27
这里Date是一个datetime变量。
当前回答
由于没有指定时间段的datetime将具有date 00:00:00.000的值,如果您希望确保获得范围内的所有日期,则必须为结束日期提供时间,或者增加结束日期并使用<。
select Date,TotalAllowance from Calculation where EmployeeId=1
and Date between '2011/02/25' and '2011/02/27 23:59:59.999'
OR
select Date,TotalAllowance from Calculation where EmployeeId=1
and Date >= '2011/02/25' and Date < '2011/02/28'
OR
select Date,TotalAllowance from Calculation where EmployeeId=1
and Date >= '2011/02/25' and Date <= '2011/02/27 23:59:59.999'
不要使用下面的语句,因为如果它们的时间是00:00:00.000,它可能会返回一些从2011/02/28开始的记录。
select Date,TotalAllowance from Calculation where EmployeeId=1
and Date between '2011/02/25' and '2011/02/28'
其他回答
select Date,TotalAllowance
from Calculation
where EmployeeId=1
and convert(varchar(10),Date,111) between '2011/02/25' and '2011/02/27'
Select
*
from
Calculation
where
EmployeeId=1 and Date between #2011/02/25# and #2011/02/27#;
SELECT Date, TotalAllowance
FROM Calculation
WHERE EmployeeId = 1
AND Date BETWEEN to_date('2011/02/25','yyyy-mm-dd')
AND to_date ('2011/02/27','yyyy-mm-dd');
我会选择
select Date,TotalAllowance from Calculation where EmployeeId=1
and Date >= '2011/02/25' and Date < DATEADD(d, 1, '2011/02/27')
逻辑是>=包括整个开始日期,<不包括结束日期,因此我们在结束日期上添加一个单位。这可以适应几个月,例如:
select Date, ... from ...
where Date >= $start_month_day_1 and Date < DATEADD(m, 1, $end_month_day_1)
如果日期在24小时内,从早上开始,到晚上结束,应该添加如下内容:
declare @Approval_date datetime
set @Approval_date =getdate()
Approval_date between @Approval_date +' 00:00:00.000' and @Approval_date +' 23:59:59.999'