如何从SQL Server 2005中的日期获取月份和年份? [英] How to get both month and year together from a date in sql server 2005?

问题描述

大家好，

对于以下查询(日期是列名，表名是storedata)

Hi All,

For the below query (sdate is column name and table name is storedata)

WITH TotalMonths AS (SELECT T1.[Month], T2.[Year]
 FROM ((SELECT DISTINCT Number AS [Month]
FROM MASTER.dbo.spt_values WHERE [Type] = 'p' AND Number BETWEEN 1 AND 12) T1 CROSS JOIN
 (SELECT DISTINCT DATEPART(year, sdate) AS [Year]
FROM storedata) T2))

SELECT CTE.[Year], CTE.[Month], ISNULL(T3.[Sum], 0) areasum
FROM TotalMonths CTE LEFT OUTER JOIN (
SELECT SUM(areasft) [Sum], DATEPART(YEAR, sdate) [Year], DATEPART(MONTH, sdate) [Month]
FROM storedata
GROUP BY DATEPART(YEAR, sdate) ,DATEPART(MONTH, sdate)) T3
ON CTE.[Year] = T3.[Year] AND CTE.[Month] = T3.[Month] WHERE CTE.[Year]>'2007'
ORDER BY CTE.[Year], CTE.[Month]

我得到如下结果集:

I am getting result set like below:

YEAR MONTH AREASUM
2008	1	0
2008	2	1193
2008	3	4230
2008	4	350
2008	5	2200
2008	6	4660
2008	7	0
2008	8	6685
2008	9	0
2008	10	3051
2008	11	7795
2008	12	2940
2009	1	1650
2009	2	3235
2009	3	2850
2009	4	6894
2009	5	3800
2009	6	2250
2009	7	1000
2009	8	1800
2009	9	1550
2009	10	2350
2009	11	0
2009	12	1800

但是我必须将月份和年份都合并在一个列中.结果集应如下所示.

But I have to combine both month and year in single column. The reult set should like below.

JAN/08 O
FEB/08 1193
.. ..
.. ..
DEC/O9 1800

如何修改查询? (即使一个月没有面积，我也应该显示所有年份和月份)

问候，
N.SRIRAM

How can I modify my query? (I should display for all the years and months even if there is no area for a month)

Regards,
N.SRIRAM

推荐答案

我认为没有任何内置函数可以为您提供SQL模式化格式的输出.是的，可以在显示时按语言显示.

或者，您可以将两个结果结合起来，并通过自定义
使事情变得可行
喜欢

I think there''s not any inbuilt function that can provide you pattern formatted output in SQL. Yes it is available by Language at display time.

Or you can combine two result and make your things work by customizing

Like

SELECT CAST(DateName(mm,GetDate())  as varchar) + '/' + CAST(DateName(DD,GetDate())  as varchar)

您可以引用DateName 此处 [此 [

You can refer DateName HERE[^] and displaying output with pattern at language side you may find THIS[^] article to be useful.

这篇关于如何从SQL Server 2005中的日期获取月份和年份?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！