如何使用记录检索2014年，2015年和2016年之间的某些月份 [英] How do I retrieve certain months between 2014, 2015 and 2016 with records

问题描述

我们希望从2015年1月到2016年3月检索​​记录，我们尝试使用 BETWEEN 语句作为示例，如下所示



我尝试过：

 从 tbl_PMTCT 
  年>> = 2015 和年< = 2016 和月之间 1月和 3月订单 按几个月

这在c＃代码中起作用，但结果中缺少几个月。例如：2015年2月和2016年2月



我将非常感谢您在这方面的任何帮助

解决方案

问题是您的数据库设计糟糕！

您应该将日期信息存储在DATE或DATETIME列中，而不是作为单独的部分存储。它更有效率，也更容易使用。

目前，我猜你将年份存储为数字，月份为字符串：1月， 二月，三月，......

因此，当您使用BETWEEN时，它会进行基于字符串的比较，其中比较结果完全基于字符串中的第一个不同字符。由于'F'不在'J'和'M'之间，它无法返回您需要的记录。

使用DATE或DATETIME字段，查询变得明显：

  SELECT  *  FROM  tbl_PMTCT  WHERE  myDate  BETWEEN  '  2015 -01-01'  AND  '  2016 -12-31'  AND  DATEPART（mm，myDate） BETWEEN   1   AND   3  

首先，我想指出 OriginalGriff [ ^ ]的建议：您的数据库设计糟糕！

Quote：

我们想要检索2015年1月至2016年3月的记录

如果将月份存储为文本：1月，2月等，则必须以这种方式编写查询：

  SELECT  * 
  FROM  tbl_PMTCT 
  WHERE （years =  2015 ） OR （years =  2016   AND 个月 IN （'  1月'，'  2月' ，'  March' ））

另一个解决方案是为月编写帮助表，然后到连接表如下：

  DECLARE   @helper  表（MonthID  INT   IDENTITY （  1 ， 1 ），NameOfMonth  VARCHAR （  30 ））
  INSERT   INTO   @helper （NameOfMonth）
  VALUES （ '  1月'），（'  2月'），... 
 
  SELECT  p。* 
  FROM  tbl_PMTCT  AS  p  INNER   JOIN   @helper  h  ON  p.months = h.NameOfMonth 
  WHERE （p.years =  2015 ） OR （p.years =  2016   AND  h.MonthID< =  3 ）

we want to retrieve records from January 2015 to March 2016, we tried using BETWEEN statement for the months as the example bellow

What I have tried:

select *
from tbl_PMTCT
where years >=2015 and years <=2016 and months between January and March order by months

this worked in the c# code but some months where missing from the result. example are : February 2015 and February 2016

I will appreciate any help from you in this regard

解决方案

The problem is that your database is badly designed!
You should store the date information in a DATE or DATETIME column rather than as separate parts. It's a lot more efficient and a lot easier to work with.
At the moment, I'm guessing that you are storing the year as a number, and the month as a string: "January", "February", "March", ...
So when you use BETWEEN it is doing a string based comparison, where the result of the comparison is based entirely on the first different character in the string. Since 'F' isn't between 'J' and 'M', it fails to return the records you need.
Use a DATE or DATETIME field and the query becomes obvious:

SELECT * FROM tbl_PMTCT WHERE myDate BETWEEN '2015-01-01' AND '2016-12-31' AND DATEPART(mm, myDate) BETWEEN 1 AND 3

First of all, i'd like to point out to OriginalGriff[^]'s suggestion: your database is badly designed!

Quote:

we want to retrieve records from January 2015 to March 2016

If months are stored as a text: 'January', 'February', etc., you have to write query this way:

SELECT *
FROM tbl_PMTCT
WHERE (years = 2015) OR (years = 2016 AND months IN ('January', 'February', 'March'))

Another solution is to write helper table for months and then to join table as follow:

DECLARE @helper TABLE (MonthID INT IDENTITY(1,1), NameOfMonth VARCHAR(30))
INSERT INTO @helper (NameOfMonth)
VALUES('January'), ('February'), ...

SELECT p.*
FROM tbl_PMTCT AS p INNER JOIN @helper h ON p.months = h.NameOfMonth
WHERE (p.years = 2015) OR (p.years = 2016 AND h.MonthID <= 3)

这篇关于如何使用记录检索2014年，2015年和2016年之间的某些月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！