如何在sql server 2008中编写来自和迄今的查询 [英] how to write query for from and to date in sql server 2008

问题描述

在sql server 2008中，我这样的表结构，我需要根据fromdate和todate检索记录



ID Text ValidFrom ValidTo

2 A 2/3/2015 2/7/2015

3 C 2/5/2015 2/6/2015

4 B 1/21/2015 1 / 23/2015

5 D 1/23/2015 2015年1月24日





如果来自日期= '02 / 04/2015'和todate = '02 / 06/2015'然后

期待结果为

ID文本ValidFrom ValidTo

2 A 2/3/2015 2/7/2015

3 C 2/5/2015 2/6/2015

解决方案

Not确定你是否已经解决了这个问题，但在我的诚实意见中，你永远不应该在varchar中存储日期。当你需要比较值，计算它等时，这会导致你出现问题。你总是最终将文本值转换回日期，然后进行实际操作。



当您转换了值后，您的查询应该是这样的：

  SELECT  .. 。
  FROM  ... 
  WHERE   @fromDateValue   BETWEEN  ValidFrom  AND  ValidTo 
  OR   @ toDateValue   BETWEEN  ValidFrom  AND  ValidTo 

您可以过滤以下日期

  SELECT  ID。文本，ValidFrom，ValidTo 
  FROM  [YourTableName] 
  WHERE  ValidFrom  BETWEEN  '  02/04/2015'  AND  '  02/06/2015' 
   -   或 - 在哪里有效于'02/04 / 2015'和'02 / 06/2015' 

但是，这肯定不会给你所显示的结果，因为你想要的结果如

 ID Text ValidFrom ValidTo 
 2 A 2/3/2015 2/7/2015 
 3 C 2/5/2015 2/6/2015 

但是'2 / 2015年3月20日'或'2/7/2015'与过滤日期范围'02 / 04/2015'和'02 / 06/2015'不相符



如果你想要别的东西，请告诉我:)）

试试以下命令 -

  SELECT  column_name（s）
  FROM  table_name 
  WHERE  column_name  BETWEEN  value1  AND  value2; 

In sql server 2008, my table structure like this and i need to retrieve records based on fromdate and todate

ID Text ValidFrom ValidTo
2 A 2/3/2015 2/7/2015
3 C 2/5/2015 2/6/2015
4 B 1/21/2015 1/23/2015
5 D 1/23/2015 1/24/2015


if fromdate='02/04/2015' and todate='02/06/2015' then
expecting result as
ID Text ValidFrom ValidTo
2 A 2/3/2015 2/7/2015
3 C 2/5/2015 2/6/2015

解决方案

Not sure if you have solved this problem already, but in my honest opinion you should never store dates in varchar. This would lead you to problems whenever you need to compare the values, calculate it etc. You always end up to converting the text values back to dates and then doing the actual operation.

When you have converted the values then your query should be something like:

SELECT ...
FROM ...
WHERE @fromDateValue BETWEEN ValidFrom AND ValidTo
OR    @toDateValue BETWEEN ValidFrom AND ValidTo

You can filter dates like following

SELECT ID.Text,ValidFrom,ValidTo
FROM [YourTableName]
WHERE ValidFrom BETWEEN '02/04/2015' AND '02/06/2015' 
--OR--WHERE ValidTo BETWEEN '02/04/2015' AND '02/06/2015'

But, this will definitely not give the result as you have shown because as you want result like

ID Text ValidFrom ValidTo
2 A 2/3/2015 2/7/2015
3 C 2/5/2015 2/6/2015 

but '2/3/2015' or '2/7/2015' of first row doesn't match with filter date range '02/04/2015' and '02/06/2015'

If you want something else, please let me know :)

Try following command-

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

这篇关于如何在sql server 2008中编写来自和迄今的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！