to get the months from year - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

to get the months from year

Author

Topic

amarnathreddy.midde
Starting Member

7 Posts

Posted - 2008-07-17 : 03:34:31

hello,
I need to get the all months from given year output should be like
1 jan
2 feb
3 mar
4 apr
5 may
6 jun
7 jul
9 aug
9 sep
10 oct
11 nov
12 dec
i have to get the output in the above format for any given year.Plese give me solution as soon as possible.ineed it urgent

amar

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 03:38:33

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 03:41:47

[code]SELECT NUMBER, DATENAME(MONTH, DATEADD(MONTH, NUMBER - 1, 0))
FROM dbo.F_TABLE_NUMBER_RANGE (1, 12)
ORDER BY NUMBER
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 03:42:11

[code]DECLARE @year varchar(4)--to hold year value
set @year='2008'--entered value by user

select DATEPART(mm,DATEADD(m,number,@year)) as MonthVal,LEFT(DATENAME(mm,DATEADD(m,number,@year)),3) AS MonthName
from master..spt_values
where type='p'
and number<=11[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 03:48:37

using CTE:-

DECLARE @year varchar(4)
set @year='2008'


;
With Month_CTE (Date,MonthVal,MonthName,Level) as
(
select DATEADD(m,0,@year),DATEPART(mm,DATEADD(m,0,@year)) as MonthVal,LEFT(DATENAME(mm,DATEADD(m,0,@year)),3),1
UNION ALL
select DATEADD(m,1,Date),DATEPART(mm,DATEADD(m,1,Date)) as MonthVal,LEFT(DATENAME(mm,DATEADD(m,1,Date)),3),Level + 1
FROM Month_CTE
WHERE Level <=11
)

SELECT MonthVal,MonthName FROM Month_CTE


output
----------------------------
MonthVal    MonthName
----------- ---------
1           Jan
2           Feb
3           Mar
4           Apr
5           May
6           Jun
7           Jul
8           Aug
9           Sep
10          Oct
11          Nov
12          Dec

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 05:15:18

quote:
Originally posted by amarnathreddy.midde

hello,
I need to get the all months from given year output should be like
1 jan
2 feb
3 mar
4 apr
5 may
6 jun
7 jul
9 aug
9 sep
10 oct
11 nov
12 dec
i have to get the output in the above format for any given year.Plese give me solution as soon as possible.ineed it urgent

amar

If you want to have the result to join with other tables frequently, better create a new table having those data

Madhivanan

Failing to plan is Planning to fail

amarnathreddy.midde
Starting Member

7 Posts

Posted - 2008-07-17 : 05:40:01

Thank you so much its working trying to understand that function my project need is over thank s a lot

amar

amarnathreddy.midde
Starting Member

7 Posts

Posted - 2008-07-17 : 06:25:11

if u dont mind could u explain me the function that u used here

amar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 06:43:01