We help IT Professionals succeed at work.

I need help with a Table Variable in SQL Server

mainrotor
mainrotor used Ask the Experts™
on
Hi all,
I need assistance creating a Table Variable that will hold a List of weekdays for the date range of 1/1/2011 to 6/1/2011.  This should be a one column table called tblBusinessDays, and should exclude all Saturdays and Sundays for the given date range.  How can I do this?

Thanks in advance,
mrotor
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
Please try the following:

Set Nocount On

Declare @table table (dt date)

Insert into @table (dt)
	Select dateadd(day, v.number, '1/1/2011') dt
	  From [master].[dbo].[spt_values] v
	 Where v.type = 'P'
	   and dateadd(day, v.number, '1/1/2011') <= '6/1/2011'
   
Select * From @table

Open in new window


Output
dt
----------
2011-01-01
2011-01-02
2011-01-03
2011-01-04
2011-01-05
2011-01-06
2011-01-07
2011-01-08
2011-01-09
2011-01-10
2011-01-11
2011-01-12
2011-01-13
2011-01-14
2011-01-15
2011-01-16
2011-01-17
2011-01-18
2011-01-19
2011-01-20
2011-01-21
2011-01-22
2011-01-23
2011-01-24
2011-01-25
2011-01-26
2011-01-27
2011-01-28
2011-01-29
2011-01-30
2011-01-31
2011-02-01
2011-02-02
2011-02-03
2011-02-04
2011-02-05
2011-02-06
2011-02-07
2011-02-08
2011-02-09
2011-02-10
2011-02-11
2011-02-12
2011-02-13
2011-02-14
2011-02-15
2011-02-16
2011-02-17
2011-02-18
2011-02-19
2011-02-20
2011-02-21
2011-02-22
2011-02-23
2011-02-24
2011-02-25
2011-02-26
2011-02-27
2011-02-28
2011-03-01
2011-03-02
2011-03-03
2011-03-04
2011-03-05
2011-03-06
2011-03-07
2011-03-08
2011-03-09
2011-03-10
2011-03-11
2011-03-12
2011-03-13
2011-03-14
2011-03-15
2011-03-16
2011-03-17
2011-03-18
2011-03-19
2011-03-20
2011-03-21
2011-03-22
2011-03-23
2011-03-24
2011-03-25
2011-03-26
2011-03-27
2011-03-28
2011-03-29
2011-03-30
2011-03-31
2011-04-01
2011-04-02
2011-04-03
2011-04-04
2011-04-05
2011-04-06
2011-04-07
2011-04-08
2011-04-09
2011-04-10
2011-04-11
2011-04-12
2011-04-13
2011-04-14
2011-04-15
2011-04-16
2011-04-17
2011-04-18
2011-04-19
2011-04-20
2011-04-21
2011-04-22
2011-04-23
2011-04-24
2011-04-25
2011-04-26
2011-04-27
2011-04-28
2011-04-29
2011-04-30
2011-05-01
2011-05-02
2011-05-03
2011-05-04
2011-05-05
2011-05-06
2011-05-07
2011-05-08
2011-05-09
2011-05-10
2011-05-11
2011-05-12
2011-05-13
2011-05-14
2011-05-15
2011-05-16
2011-05-17
2011-05-18
2011-05-19
2011-05-20
2011-05-21
2011-05-22
2011-05-23
2011-05-24
2011-05-25
2011-05-26
2011-05-27
2011-05-28
2011-05-29
2011-05-30
2011-05-31
2011-06-01

Open in new window


I hope this helps.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:

Author

Commented:
These examples are goob but neither excludes weekends.
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
have you seen the @SkipWeekend parameter?
Maybe you can try this tweak from wdosanjos's query:

Set Nocount On

Declare @table table (dt date)

Insert into @table (dt)
      Select dateadd(day, v.number, '1/1/2011') dt
        From [master].[dbo].[spt_values] v
       Where v.type = 'P'
         and dateadd(day, v.number, '1/1/2011') <= '6/1/2011'
and datename(dw,dateadd(day, v.number, CONVERT(DATETIME,'2011-01-01',102))) not in ('Saturday','Sunday')
   
Select * From @table
Top Expert 2011
Commented:
Alternatively:

Set Nocount On
Set DateFirst 7 -- Sunday

Declare @table table (dt date)

Insert into @table (dt)
	Select dt
	  From (Select dateadd(day, v.number, '1/1/2011') dt
	          From [master].[dbo].[spt_values] v
	         Where v.type = 'P'
	           and dateadd(day, v.number, '1/1/2011') <= '6/1/2011') r
	 Where datepart(dw, dt) between 2 and 6
   
Select * From @table

Open in new window