Finding weekdays of a given month
-
Hi gurus,
Can you please help me in finding all the weekdays of a given month and a year.
For Example if there are 2 input parameters of a stored proc - 'April' and '2005'
I would like a recordset of 21 work weekdays(mon to Fri) for the month of April in 2005.
it would give me a recordset like
04/01/2005
04/04/2005
04/05/2005
04/06/2005
.........
04/29/2005
Any help would be greatly appreciated.
thanks,
Tania
-
Create a calendar table (all dates of the year for any range of years needed then :
Select Date from dbo.Calendar where Date bewteen @StartRange and @EndRange and datepart(dw, Date)< 6
will produce a clustered index seek.
full script :
IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U' and user_name(uid) = 'dbo')
DROP TABLE Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
IF EXISTS (Select * from dbo.SysObjects where name = 'Calendar' and XType = 'U' and user_name(uid) = 'dbo')
DROP TABLE Calendar
GO
Create table dbo.Calendar (DateCal SmalldateTime not null primary key clustered)
GO
DECLARE @DateStart as smalldatetime
SET @DateStart = '2000/01/01'
--this fills almost 22 years at the time
Insert into dbo.Calendar (DateCal) Select DateAdd(D, PkNumber, @DateStart) from dbo.Numbers
Select DateCal from dbo.Calendar where DateCal between '2005/04/01' and '2005/04/30' and datepart(dw, DateCal)< 6
/*
2005-04-01 00:00:00
2005-04-04 00:00:00
2005-04-05 00:00:00
2005-04-06 00:00:00
2005-04-07 00:00:00
2005-04-08 00:00:00
2005-04-11 00:00:00
2005-04-12 00:00:00
2005-04-13 00:00:00
2005-04-14 00:00:00
2005-04-15 00:00:00
2005-04-18 00:00:00
2005-04-19 00:00:00
2005-04-20 00:00:00
2005-04-21 00:00:00
2005-04-22 00:00:00
2005-04-25 00:00:00
2005-04-26 00:00:00
2005-04-27 00:00:00
2005-04-28 00:00:00
2005-04-29 00:00:00
*/
-
Another possible solution might be the following:
DECLARE @Month varchar(10),
@Year varchar(4),
@Date char(2),
@FinalDate datetime,
@LastDayOfMonth char(2)
SET @Month = 'April'
SET @Year = '2005'
SET @Date = 1
SET @LastDayOfMonth = (SELECT CONVERT( char(2), DATEPART( day,
DATEADD( month, 1, CONVERT( datetime, @Month + ' ' +
RTRIM( LTRIM( @Date)) + ', ' + @Year)) - 1)))
CREATE TABLE #Dates( [Date] datetime)
WHILE CONVERT( integer, @Date) <= CONVERT( integer, @LastDayOfMonth)
BEGIN
SELECT @FinalDate = (SELECT CONVERT( datetime, @Month + ' ' +
RTRIM( LTRIM( @Date)) + ', ' + @Year))
INSERT INTO #Dates( [Date])
SELECT @FinalDate WHERE DATENAME( weekday, @FinalDate) NOT IN( 'Saturday', 'Sunday')
SELECT @Date = @Date + 1
END
SELECT CONVERT( varchar, [Date], 101) FROM #Dates
DROP TABLE #Dates
I wasn't born stupid - I had to study.
-
Hey bulldozer... been a while
.I think my solution is slightly faster that Farrell's
. -
CREATE PROCEDURE usp_WeekDays
(
@pMonth INT = NULL,
@pYear INT = NULL
)
AS
SET NOCOUNT ON
DECLARE @Days INT
DECLARE @CurDate DATETIME
DECLARE @MyDates TABLE
(
MyDate DATETIME
)
SET DATEFORMAT MDY
SELECT @pMonth = COALESCE(@pMonth, DATEPART(MONTH, GETDATE()))
SELECT @pYear = COALESCE(@pYear, DATEPART(YEAR, GETDATE()))
SELECT @Days = 0
SET @CurDate = CONVERT(VARCHAR, @pMonth) + '-01-' + CONVERT(VARCHAR, @pMonth)
WHILE DATEPART(MONTH, @CurDate) = @pMonth
BEGIN
INSERT INTO @MyDates (MyDate) VALUES (@CurDate)
SELECT @CurDate = DATEADD(DAY, 1, @CurDate)
END
SELECT CONVERT(VARCHAR, MyDate, 101) [WeekDate],
DATENAME(WEEKDAY, MyDate) [WeekDay]
FROM
@MyDates
WHERE
DATENAME(WEEKDAY, MyDate) NOT IN ('Sunday', 'Saturday')
ORDER BY MyDate
GO
/* Working days for current month */
EXEC usp_WeekDays
Go
/* Working days for any month year*/
EXEC usp_WeekDays 6, 2005
Go
/* Clean Up */
DROP PROC usp_WeekDays
My thinking is slower than Farrel
Regards,
gova -
Probably correct Remi - and it is always good to have a calendar table. But I was just giving an alternative if the person wanted to simply pass in the word "April" and the year as a character field.
We have a dba where I am currently contracting that restricts too much and probably wouldn't allow a Calendar table - he's a bit difficult.
So sometimes it helps to find work arounds. (Have family in from Australia, (and working hard the days I have to come in) so not much time to post - sushila has been covering my behind on another posting where it has been a bit difficult to get exactly what the person wants...).
I wasn't born stupid - I had to study.
-
You can always use the numbers table instead and recreate the dates on the fly just like I did to populate the table.
If your DBA doesn't want a numbers table either you can always build it on the fly with derived tables + cross joins.
BTW numbers table = 32K on ram and maybe 100k on the HD. Why the h3ll can't he put that on the system???
-
We have a dba .... - he's a bit difficult
One of the expected characteristics of a good DBA! Not saying that if you are not difficult you may not be good though but that tends to be mostly the opinion that consultants have from me when I have been in the DBA side of the table
... I like to defend my DBs
* Noel -
Damn I'm gonna be god one day... I pretty much have a gun waiting right beside the server for anyone who wants to come close
.Anyways I can understand that a calendar table might take up some room, but a numbers table should be no problem, I mean who can't spare 80K on the hd/ram??
-
If you need something more comprehensive, there's an Auxiliary Calendar Table posted on ASPFAQ at http://www.aspfaq.com/show.asp?id=2519
It codes weekends, weekdays, holidays, etc. for you, and has sample SQL Queries showing how to calculate things like: "When should I expect delivery of X?", "How many working days until Y?" and "How many Fridays are in this Quarter?"
-
Don't misunderstand me. I agree that "being difficult" is one of the jobs of a good DBA. But this guy is beyond that. He will make up rules without good basis, (for instance, NO WHILE LOOPS, ever...), and then give no alternatives.
I have fought him on a number of occassions using BOL and ANSI standard documents to prove his standards were wrong. But with Gov't, it takes a while to get changes into place. Oil well - life would be boring without some stress....
He has dropped Roles on us without telling us and many of our systems broke. It took weeks of testing because we had no idea he had done this. He just found it one day, did not know what it was, so he dropped it... Tax dollars well spent, huh?
So, there is difficult and there is difficult...
I wasn't born stupid - I had to study.
-
I see why... you are using them a lot, even if you try to avoid it. I can only imagine someone who doesn't want to learn the set based way. In my system I have only admin procs that need looping (for executing statements). Other than that everything is set.
-
No we aren't. It is simply a hard and fast rule of his.
While I agree that set based is best, it is not the only solution. Hence, not allowing things like looping is foolish, (limit: yes, don't allow: no). This is also a legacy system based upon conversion from Mainframe to SQL Server and that is not always done with good design. Normalization was not always followed or understood when the people who converted the data finalized that task...
My wife found this one time on SQL and I have found it a useful definition:
As the name implies, SQL is designed for a specific, limited purpose — querying data contained in a relational database. As such, it is a set-based, declarative computer language rather than an imperative language such as C or BASIC which, being programming languages, are designed to solve a much broader set of problems. Language extensions such as PL/SQL are designed to address this by turning SQL into a full-fledged programming language while maintaining the advantages of SQL. Another approach is to allow programming language code to be embedded in and interact with the database. For example, Oracle and others include Java in the database, while PostgreSQL allows functions to be written in a wide variety of languages, including Perl, Tcl, and C.
I wasn't born stupid - I had to study.
-
You know your system better than me... but I still have to find a problem that can't be resolved with a set solution (administrative task aside).
Thanx for the definition.
-
Here's another version (tweak as needed):
Of course, you can pass in the dates as parameters instead of hardcoding.
CREATE TABLE #Table1
(WeekDate datetime NULL,
DayOfWeek char(10) NULL) ON [PRIMARY]
declare @EndDate datetime,
@WeekDate datetime
set @WeekDate = '2005-04-01'
set @EndDate = '2005-04-30'
WHILE @WeekDate < '2005-04-30'
BEGIN
insert into #Table1
SELECT
@WeekDate,
DATENAME(weekday, @WeekDate)
WHERE DATENAME(weekday, @WeekDate) in ('Monday','Tuesday','Wednesday','Thursday','Friday')
set @WeekDate = @WeekDate + 1
END
select WeekDate, DayOfWeek from #table1
Gives these results:
WeekDate DayOfWeek
------------------------------------------------------ ----------
2005-04-01 00:00:00.000 Friday
2005-04-04 00:00:00.000 Monday
2005-04-05 00:00:00.000 Tuesday
2005-04-06 00:00:00.000 Wednesday
2005-04-07 00:00:00.000 Thursday
2005-04-08 00:00:00.000 Friday
2005-04-11 00:00:00.000 Monday
2005-04-12 00:00:00.000 Tuesday
2005-04-13 00:00:00.000 Wednesday
2005-04-14 00:00:00.000 Thursday
2005-04-15 00:00:00.000 Friday
2005-04-18 00:00:00.000 Monday
2005-04-19 00:00:00.000 Tuesday
2005-04-20 00:00:00.000 Wednesday
2005-04-21 00:00:00.000 Thursday
2005-04-22 00:00:00.000 Friday
2005-04-25 00:00:00.000 Monday
2005-04-26 00:00:00.000 Tuesday
2005-04-27 00:00:00.000 Wednesday
2005-04-28 00:00:00.000 Thursday
2005-04-29 00:00:00.000 Friday
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply