sql Year函数问题 [英] sql Year function Problem

问题描述

我有一个查询要根据年份提取一些记录，在
的where子句中 我根据日期范围(即2011年1月1日和今天的日期)进行抓取，例如

Hi,

I have a query to fetch out some records based on the year,In the where clause when
i am fetching on the basis of Date range i.e,1st Jan''2011 and Today''s Date like

CONVERT(VARCHAR,REQDATE,101) BETWEEN CONVERT(VARCHAR,'01/01/2011',101) AND CONVERT(VARCHAR,'11/12/2011',101)

它给出了确切的行数，但是当我使用像Year(a.REQDATE )= 2011
这样的年份函数时 它没有给出确切的数字...可能是什么情况??

有人可以帮忙吗????

谢谢，
Prasant

Its giving exact amount of rows but when i''m using year function like Year(a.REQDATE )= 2011
Its not giving the exact count...what could be the scenario???

Can any one help on this Please???

Thanks,
Prasant

推荐答案

如果您的列是datetime，则只需删除不必要的转换:

Just remove the unnecessary conversion if your column is a datetime :

SELECT DateName(month,a.REQDATE) As hp ,Month(a.REQDATE) months, count(*) as counts 
FROM TASKS a INNER JOIN WorkOrderStatus b ON a.WorkOrderStatusId = b.WorkOrderStatusId WHERE 
a.REQDATE between '01-01-2011' and '11-14-2011'
GROUP BY DateName(month,a.REQDATE) ,Month(a.REQDATE) ORDER BY Month(a.REQDATE) ASC

HI，
这是ms-sql中Year函数的简单概述.
http://msdn.microsoft.com/en-us/library/ms186313.aspx [ ^ ]

当我检查您的sql查询时，我认为datetime字段到varchar的转换存在一个问题.
因为这取决于SQL Server系统的日期时间格式，所以有时我们将datetime字段转换为varchar时，它将逐月和逐月转换，因此您的sql查询和db结构可能会出现问题

this is simple overview of Year function in ms-sql.
http://msdn.microsoft.com/en-us/library/ms186313.aspx[^]

as i check you sql query and i think that there is one issue with conversion of datetime field to varchar.
because it is depends upon the datetime format of sql server''s system sometime it when we convert datetime field to varchar it will convert month to day and day to month so might be something like issue will be happen with your sql query and db structure.

这是使用DATEPART的另一种方法:

And here is another approach using DATEPART :

SELECT DateName(month,a.REQDATE) As hp ,Month(a.REQDATE) months, count(*) as counts FROM TASKS a INNER JOIN WorkOrderStatus b ON a.WorkOrderStatusId = b.WorkOrderStatusId WHERE  a.REQUEST = 'HPAD' and DATEPART(yy ,a.REQDATE)=2011 GROUP BY DateName(month,a.REQDATE) ,Month(a.REQDATE) ORDER BY Month(a.REQDATE) ASC

这篇关于sql Year函数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！