如何在运营商之间使用日期 [英] How to use between operator for dates

问题描述

select * From Table BETWEEN '23 / 3/2016'和'23 / 3/2016'



我尝试过：



我正在尝试运行此查询，但它为我提供了两个日期之间的数据，并没有显示第一个和最后一个日期的数据

select * From Table where Date BETWEEN '23/3/2016' AND '23/3/2016'

What I have tried:

I'm trying to run this query but it gives me the data between two dates and does not show me the data for 1st and last date

推荐答案

我看到你已经找到了一个解决方案，但我认为值得对正在发生的事情做出解释。



表中的 Date 列可能是包含日期和时间的 DateTime 。如果在查询中使用没有时间的日期，则时间部分将隐式设置为00:00:00。所以有效的查询将是

I saw that you found a solution already but I think it is worth to give an explanation on what is happening.

The Date column from your table is probably a DateTime containing date and time. If you use a date without time in your query, the time portion is implicitly set to 00:00:00. So the effective query will be

select * From Table where Date BETWEEN '23/3/2016 00:00:00' AND '23/3/2016 00:00:00'

这在使用比较而非 BETWEEN 时也适用。



所以有两种常见的解决方案：



使用 BETWEEN 结束日期的时间：

This applies also when using a comparison rather BETWEEN.

So there are two common solutions:

Use BETWEEN with time for end date:

select * From Table where Date BETWEEN '23/3/2016' AND '23/3/2016 23:59:59.999'

或者使用少于第二天结束日期的比较：

Or use less than comparison for the end date with the following day:

select * From Table where Date >= '23/3/2016' AND Date < '24/3/2016'

我使用此类型查询获取所有结果以及第一个日期和最后日期

I Have Use this type query for get all result between and also first date and last date

select * From Table where Date >='23/3/2016' AND  Date <='23/3/2016' 

select * from non_gia_search_history where convert(varchar(10),create_time,110)>='03-15-2016' and convert(varchar(10),create_time,110) <='03-19-2016'

我找到了这个解决方案



select * From Table DATEDIFF（DAY，ColName，23 / 3/2016）< = 0 AND DATEDIFF（DAY，ColName，23 / 3/2016）> = 0

I found this solution

select * From Table where DATEDIFF(DAY,ColName,23/3/2016) <=0 AND DATEDIFF(DAY,ColName,23/3/2016)>=0

这篇关于如何在运营商之间使用日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！