March 8, 2012 at 9:40 am
Hi,
I am looking for a function to identify whether last day of the previous month is weekday or a weekend.
If it is a weekend then it should return the next weekday.
For example. 31/12/2011 was saturday.
The function should return 02/01/2012 as it was the next working day(Monday).
If it was a weekday then it should return the date.
Kindly let me know the function.
Thanks,
Sandesh
March 8, 2012 at 9:58 am
March 8, 2012 at 10:02 am
declare @dt datetime
set @dt = '20120101'
SELECT DATEADD(DAY,
CASE DATENAME(WEEKDAY,DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,@dt),0)))
WHEN 'Saturday' THEN 2
WHEN 'Sunday' THEN 1
ELSE 0
END,
DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,@dt),0)))
--prev month to given day was Dec 2011, its last day (31th) was Saturday,
-- so 02 Jan 2012 is returned
March 8, 2012 at 10:18 am
I would not rely on DATENAME.
Try prefixing that code with SET LANGUAGE Italian:
L'impostazione della lingua è stata sostituita con Italiano.
-----------------------
2011-12-31 00:00:00.000
(1 row(s) affected)
-- Gianluca Sartori
March 8, 2012 at 10:32 am
Gianluca Sartori (3/8/2012)
I would not rely on DATENAME.Try prefixing that code with SET LANGUAGE Italian:
L'impostazione della lingua è stata sostituita con Italiano.
-----------------------
2011-12-31 00:00:00.000
(1 row(s) affected)
I wouldn't too, if my database would continuously change the language settings during the day...
I guess if OP is in Italy, he can use Italian word for that.
You can also add, that not every country has a weekend on Saturday and Sunday...
March 8, 2012 at 11:16 am
The calendar table allows you to more easily handle holidays. The fact that the OP said he wanted to return the "next working day (Monday)," indicates that he needs that capability.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 8, 2012 at 11:21 am
Eugene Elutin (3/8/2012)
I wouldn't too, if my database would continuously change the language settings during the day...
😀 You made my day!
I can go home happy now!
-- Gianluca Sartori
March 8, 2012 at 8:37 pm
Gianluca Sartori (3/8/2012)
Eugene Elutin (3/8/2012)
I wouldn't too, if my database would continuously change the language settings during the day...😀 You made my day!
I can go home happy now!
Spaghetti and Porkchops for everyone! 🙂
--Jeff Moden
March 9, 2012 at 7:19 am
Sounds tasty!
A bit hypercaloric, but tasty! 😛
-- Gianluca Sartori
March 9, 2012 at 11:06 am
You need to add this function
CREATE Function [dbo].[Get_MyDate]( @dDate DateTime ) Returns DateTime
AS
begin
declare @mDate DateTime,@m1 Int,@m2 Int,@d1 Int
Set @m1 = DATEPART(m,@dDate)
Set @m2 = DATEPART(m,@dDate+2)
if @m1=@m2
begin
set @mDate = @dDate
end
else
begin
Set @d1 = DATEPART(dw,@dDate)
if @d1=7 set @mDate = @dDate+2
else if @d1=1 set @mDate = @dDate+1
else set @mDate = @dDate
end
return @mDate
end
then to run it
Select Get_myDate('31/12/2011')
March 10, 2012 at 9:21 am
siva 20997 (3/9/2012)
You need to add this function
CREATE Function [dbo].[Get_MyDate]( @dDate DateTime ) Returns DateTime
AS
begin
declare @mDate DateTime,@m1 Int,@m2 Int,@d1 Int
Set @m1 = DATEPART(m,@dDate)
Set @m2 = DATEPART(m,@dDate+2)
if @m1=@m2
begin
set @mDate = @dDate
end
else
begin
Set @d1 = DATEPART(dw,@dDate)
if @d1=7 set @mDate = @dDate+2
else if @d1=1 set @mDate = @dDate+1
else set @mDate = @dDate
end
return @mDate
end
then to run it
Select Get_myDate('31/12/2011')
It doesn't work correctly. Try the following...
SELECT dbo.Get_MyDate('4/1/2012')
The last date of the previous month compared to 4/1/2012 is 3/31/2012 which is a Saturday. According to the OP's requirements, it should return the next weekday after that which, of course, should be Monday, 4/2/2012 and not the 4/1/2012 that your function returns.
--Jeff Moden
March 10, 2012 at 3:29 pm
For our friends not having English based servers, here's a rendition of Eugene's function which is not language based. Well, except maybe for our friends that use the Hijri calendar.
DECLARE @pDateTime DATETIME; --This could be a parameter for a scalar or iTVF function.
SELECT @pDateTime = '20120401';
WITH
ctePrevMonthEnd AS
(SELECT PrevMonthEnd = DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@pDateTime),0)))
SELECT DATEADD(dd,
CASE DATEDIFF(dd,0,PrevMonthEnd)%7
WHEN 5 THEN 2
WHEN 6 THEN 1
ELSE 0
END,
PrevMonthEnd)
FROM ctePrevMonthEnd
;
--Jeff Moden
March 10, 2012 at 4:08 pm
Ok... my turn for a question, folks. Does anyone have a way to do this particular task using a Calendar Table? It doesn't even have to include holidays. I just want to see someone do it because I typically wouldn't use a Calendar Table for such a thing.
And, no... I'm not bad mouthing anyone's suggestions to use a Calendar Table for this. I'd just like to see it done using one.
--Jeff Moden
March 11, 2012 at 4:37 am
The last date of the previous month compared to 4/1/2012 is 3/31/2012 which is a Saturday. According to the OP's requirements, it should return the next weekday after that which, of course, should be Monday, 4/2/2012 and not the 4/1/2012 that your function returns.
On my system 31st March 2012 returns 2nd April 2012
March 11, 2012 at 7:33 am
Jeff Moden (3/10/2012)
Ok... my turn for a question, folks. Does anyone have a way to do this particular task using a Calendar Table? It doesn't even have to include holidays. I just want to see someone do it because I typically wouldn't use a Calendar Table for such a thing.And, no... I'm not bad mouthing anyone's suggestions to use a Calendar Table for this. I'd just like to see it done using one.
assuming (dangerous :-)) the "calendar" table had columns pre populated with identifieers for weekday and holidays, then couldnt something like this work?
DECLARE @DateEnq AS DATETIME
SELECT MIN(calendar_date) AS result
FROM Calendar
WHERE (is_weekday = 1)
AND (is_holiday = 0)
AND (calendar_date >= (Dateadd(mm, Datediff(mm, 0, @dateenq), 0)) -1)
-- calendar date greater than or equal to last day of previous month
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply