Current Month - 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)

Current Month

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-31 : 08:51:26

Hi

I need query to find current month all Saturday's & sunday's

Please help on this...

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 08:56:49

One of the methods

select
dateadd(day,n-1,dateadd(month,datediff(month,0,getdate()),0)) as date,
datename(weekday,dateadd(day,n-1,dateadd(month,datediff(month,0,getdate()),0))) as day_name from
(
select row_number() over (order by name) as n from sysobjects
) as t
where n between 1 and day(dateadd(month,datediff(month,0,getdate())+1,-1))
and datename(weekday,dateadd(day,n-1,dateadd(month,datediff(month,0,getdate()),0))) in ('saturday','sunday')

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-31 : 09:00:18

[code]

SELECT DATEADD(d,number,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
FROM master..spt_values
WHERE TYPE ='p'
AND DATEADD(d,number,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) <= DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)
AND (DATENAME(DW, DATEADD(d,number,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) = 'Sunday' OR
DATENAME(DW, DATEADD(d,number,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) = 'Saturday' )
[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-31 : 09:27:51

Hi,

declare @startmonth datetime,@endmonth datetime
select @startmonth = '8/1/2009' ,@endMonth = '8/31/2009'

SELECT DATEADD(DAY, number, @startmonth)
FROM Master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @startmonth) <= @endMonth
AND datepart(dw,DATEADD(DAY, number, @startmonth)) IN(1,7)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources