如何从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

推荐答案

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中的日期获取月份和年份?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！