Display the All Dates with Day in Given Month and

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 2008 Forums

Transact-SQL (2008)

Display the All Dates with Day in Given Month and

Author

Topic

chinlax
Starting Member

30 Posts

Posted - 2011-10-12 : 02:08:41

Hi All,

I want to display the All Dates with Day in Given Month and Current Year in this format:
1-Saturday
2-Sunday
...
31- Monday
I/P : 1 (Month is "January" and Year is CurrentYear)

how to do it , can any one help me out............

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-12 : 02:14:49

[code]
declare @year int,
@month int

select @year = 2011,
@month = 10

select [DAY] = number,
[WEEKDAY] = datename(weekday, dateadd(month, (@year - 1900) * 12 + @month - 1, number - 1))
from master..spt_values
where type = 'P'
and number between 1 and day(dateadd(month, (@year - 1900) * 12 + @month, -1))
[/code]

or

just use this function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2011-10-12 : 02:30:31

/*

Exec get_month 10
*/

Create procedure get_month
@month_no int
as
Begin
Declare @i int
Declare @last_day int
Set @i=1
set @last_day =day(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))

While (@i<=@last_day)
Begin

Select convert(varchar(2),@month_no)+'/'+convert(varchar(2),@i)+'/'+convert(varchar(4),YEAR(getdate())) as [date],

DATENAME(dw,convert(varchar(2),@month_no)+'/'+convert(varchar(2),@i)+'/'+convert(varchar(4),YEAR(getdate()))) as [day]
Set @i=@i+1
End

End

Senthil.C
------------------------------------------------------
MCTS - [Microsoft][ODBC SQL Server Driver]Operation canceled

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-12 : 02:40:10

quote:
Originally posted by senthil_nagore

/*

Exec get_month 10
*/

Create procedure get_month
@month_no int
as
Begin
Declare @i int
Declare @last_day int
Set @i=1
set @last_day =day(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))

While (@i<=@last_day)
Begin

Select convert(varchar(2),@month_no)+'/'+convert(varchar(2),@i)+'/'+convert(varchar(4),YEAR(getdate())) as [date],

DATENAME(dw,convert(varchar(2),@month_no)+'/'+convert(varchar(2),@i)+'/'+convert(varchar(4),YEAR(getdate()))) as [day]
Set @i=@i+1
End

End

Senthil.C
------------------------------------------------------
MCTS - [Microsoft][ODBC SQL Server Driver]Operation canceled

That will return multiple result set. You could insert the intermediate result into a table variable or temp variable and return the result at the end of the stored procedure

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 03:02:31

[code]
DECLARE @Month int,@year int,@startdate datetime,@enddate datetime
SET @Month = 1, @Year=2011

SELECT @startdate=DATEADD(mm,@Month-1,Dateadd(yy,@Year-1900,0)),
@enddate=DATEADD(mm,@Month,Dateadd(yy,@Year-1900,0))

SELECT [Date],[Day]
FROM dbo.CalendarTable(@startdate,@enddate,0,0)

see code for calendar table below
http://visakhm.blogspot.com/2010/02/generating-calendar-table.html[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources