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 2000 Forums
 SQL Server Development (2000)
 Using Date function with GetDate()

Author  Topic 

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-09-22 : 08:37:21
Guys,
I have unique scenario here where I have to populate a column with first businees day of the year from years 1800 to 2005, I have been trying to use date function with getdate() but it doesnt seem to help.

My query returns only the first Monday of each year but as you first mon might be Jan 1st in which case it cannot be a business day.

Any suggestions/input would help

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 08:49:07
See if these are helpful
http://weblogs.sqlteam.com/brettk/archive/2004/12/20/3751.aspx
http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-22 : 08:55:13
Business days in 1800??? "Another day of burning witches, another .12 cents>"

Well I guess burning witches was some time before 1800, but seriously, do you need to take into account any day in that time frame where "businesses" were closed? ie: because of natural disasters, wars etc.? I guess Martin Luther King day wasn't too closely observed considering slavery was still very popular with the white crowd.

So you would really need a table of holidays/days off that applies to the current year (and place of interest) then apply that to your function. Since there are only 205 rows you need, just use your function to populate a table and then update the exceptions.

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-22 : 09:38:22
Since I was wise-crackin' I thought I'd offer up a possible solution.

If the links Madhivanan don't quite get you there...

for just these 2 simple rules:
1. Get the first Monday of the year.
2. If first Monday is Jan 1 make Tuesday the first business day of that year

then how about this?

select fbd = --add a day for instances of Jan 1 (first business day)
dateadd(
day
,case when datepart(day, fm) = 1 then 1 else 0 end
,fm)
from (--get first monday of each year
select fm = dateadd(day
,case
when datepart(weekday, fdy) = 1 then 1
when datepart(weekday, fdy) = 2 then 0
when datepart(weekday, fdy) = 3 then 6
when datepart(weekday, fdy) = 4 then 5
when datepart(weekday, fdy) = 5 then 4
when datepart(weekday, fdy) = 6 then 3
when datepart(weekday, fdy) = 7 then 2
end
,fdy)
from (
--get first day of each year for 1800-2005
select dateadd(year, n, '1/1/1800') fdy

from (--produce enough numbers for our problem
--(you can also use an existing numbers table instead)

select n1+n2+n3+n4+n5+n6+n7+n8 n
from (select 0 n1 union select 1) n1
cross join (select 0 n2 union select 2) n2
cross join (select 0 n3 union select 4) n3
cross join (select 0 n4 union select 8) n4
cross join (select 0 n5 union select 16) n5
cross join (select 0 n6 union select 32) n6
cross join (select 0 n7 union select 64) n7
cross join (select 0 n8 union select 128) n8
) numbers
where n <= 205
) a
) a
order by 1


Be One with the Optimizer
TG
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-22 : 09:42:25
or this......

ALTER FUNCTION dbo.NextBusMonday(@InpDate DATETIME) RETURNS DATETIME
AS
BEGIN
DECLARE @Retval DATETIME
SELECT @Retval =
CASE WHEN DATEPART(dw, @InpDate) = 2 THEN @InpDate
WHEN DATEPART(dw, @InpDate) < 2 THEN DATEADD(dd, 1, @InpDate)
ELSE DATEADD(dd, 7 - DATEPART(dw, @InpDate) + 2, @InpDate)
END
RETURN @Retval
END



--*********************************************************************************************
DECLARE @StartDate DATETIME

SELECT
@StartDate = '1800-01-02'
WHILE @StartDate < GETDATE()
BEGIN
SELECT dbo.NextBusMonday(@StartDate)
SET @StartDate = DATEADD(yy, 1, @StartDate)
END



Duane.
Go to Top of Page

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-09-22 : 11:08:45
Guys,
The above solutions indeed does work for First Mondays of the year, but my issue is finding first business day (i.e. which is not a weekend or 1st Jan). Because scenario involves populating the column for backfile data with first business day.

Any suggestions/inputs would help

Thanks
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-22 : 11:24:17
>>but my issue is finding first business day (i.e. which is not a weekend or 1st Jan).

Isn't that what I provided?
quote:
1. Get the first Monday of the year.
2. If first Monday is Jan 1 make Tuesday the first business day of that year



Be One with the Optimizer
TG
Go to Top of Page

ronstone
Starting Member

32 Posts

Posted - 2005-09-22 : 16:44:05
[quote]Originally posted by TG

Business days in 1800??? "Another day of burning witches, another .12 cents>"

This made my day!

Go to Top of Page
   

- Advertisement -