SQL选择之间的日期 [英] SQL Select dates between

问题描述

嗯......



我有一个查询返回日期在特定范围内的行，即：

Hmmmmm...

I have a query that returns rows where the date is in a particular range i.e:

WHERE Date_Column BETWEEN '2015-09-14 00:00:00' AND '2015-09-14 23:59:59' 

但是我很震惊地看到这是返回的日期是'2015-09-15 00:00:00'的列。

经过一些调整我发现23 ：59：09还可以，但23:59:10不行。



还有其他人遇到这个（服务器2008）吗？



我已经重新编写查询以使用

However I was shocked to see that this was returning columns where the date was '2015-09-15 00:00:00'.
With a bit of tweaking I found that 23:59:09 is OK but 23:59:10 is not.

Has anyone else come across this (server 2008)?

I have re-written the query to use

convert(char(10),date_column,103) = '14/09/2015'

现在这很好用。



我现在必须搜索每个项目中的每个查询，以确保在其他任何地方都不使用这种识别日期范围的方法！

now and that works OK.

I will now have to search every query I have in every project to ensure that this method of identifying date ranges is not used anywhere else!

推荐答案

我认为这些天人们之间使用之间有几个原因。由于很多原因，将日期转换为文本也是一个坏主意。使用这样的符号来查找给定的一天

I don't think people use "between" these days for a few reasons. Converting your dates to text is a bad idea for lots of reasons too. Use notation like this to find for a given day

WHERE datefield >= '2015-09-14 00:00:00' AND datefield < '2015-09-15 00:00:00'

或在年月和日使用datepart。

or use datepart on the year month and day.

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