Showing posts with label weekend. Show all posts
Showing posts with label weekend. Show all posts

Thursday, March 22, 2012

Calculate weekend days between two dates.

How do I calculate the number of wends that are between two dates?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"skb" <nospamskberen@.msn.com> wrote in message
news:ezJye1qGFHA.3068@.tk2msftngp13.phx.gbl...
> How do I calculate the number of wends that are between two dates?
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||I dont think there is a direct function to do this.. but here is a way
around....(Please excause any syntex errors.. I am just writing off
the top of my head..)
-- ****************************************
****************
Declare @.startdate smalldatetime,
@.enddate smalldatetime,
@.tempdate datetime,
@.holiday smallint
set @.startdate = '01/01/2005' -- set to relevent start date
set @.enddate = '01/31/2005' -- set to relevent end date
set @.holiday = 0
Set @.tempdate = @.startdate
while @.tempdate <= @.enddate
begin
print convert(varchar, @.tempdate) + ' : ' + DateName (dw, @.tempdate )
if DateName (dw, @.tempdate ) = 'Saturday'
begin
print @.holiday
set @.holiday = @.holiday + 1
end
if DateName (dw, @.tempdate ) = 'sunday'
begin
print @.holiday
set @.holiday = @.holiday + 1
end
set @.tempdate = dateadd(d,1,@.tempdate)
end
select 'Total number of holidays between ' + convert (Varchar,
@.startdate) + ' to ' + convert (Varchar, @.enddate) + ' : ' + convert
(varchar, @.holiday)
-- ****************************************
****************|||If you only need wends remove the sunday check!!!!|||Build a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
w_in_year SMALLINT NOT NULL, -- SQL server is not ISO standard
holiday SMALLINT NOT NULL
CHECK(holiday IN (0,1)),
day_in_year SMALLINT NOT NULL,
..);
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day wends:
http://www.smart.net/~mmontes/ushols.html|||Thanks for the help...For performance I think creating a table with
wend dates is the best.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Monday, March 19, 2012

calculate date difference exclusding weekend

I want to calculate the day difference between 2 dates excluding wend.
I notice that DateDiff function, but it does not support option to excluding
wend.
Any information is great appreciated,
Souris,http://www.google.co.uk/groups?selm...br />
oups.com
David Portas
SQL Server MVP
--|||Look up the use of a calendar table for this kind of problem. This
way, you will get all the holidays.|||http://www.aspfaq.com/2519
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"souris" <soukkris@.viddotron.com> wrote in message
news:e2w1dwKLFHA.2136@.TK2MSFTNGP14.phx.gbl...
> I want to calculate the day difference between 2 dates excluding wend.
> I notice that DateDiff function, but it does not support option to
excluding
> wend.
> Any information is great appreciated,
> Souris,
>
>|||Thanks for the information,
I got it works.
It can calculate the date difference between 2 days in the table.
Is it possible to calculate the date difference in other table?
For example:
I have a table
Account_Number Opened_Date
1 03/10/2005
2 03/15/2005
Today is 03/30/2005.
I wanted calculate the days difference between today and Opened_date
excluding wend.
I tried using the table, but not lucky.
Any information is great appreciated,
Souris,
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111255234.753472.313190@.l41g2000cwc.googlegroups.com...
> http://www.google.co.uk/groups?selm... />
groups.com
> --
> David Portas
> SQL Server MVP
> --
>|||On Wed, 30 Mar 2005 18:46:12 -0500, souris wrote:

>Thanks for the information,
>I got it works.
>It can calculate the date difference between 2 days in the table.
>Is it possible to calculate the date difference in other table?
>For example:
>I have a table
>Account_Number Opened_Date
> 1 03/10/2005
> 2 03/15/2005
>Today is 03/30/2005.
>I wanted calculate the days difference between today and Opened_date
>excluding wend.
>I tried using the table, but not lucky.
>Any information is great appreciated,
Hi Souris,
Try if this works:
SELECT a.Account_Number, a.Opened_Date.
(SELECT COUNT(*)
FROM Calendar AS c
WHERE c.caldate >= a.Opened_Date
AND c.caldate < CURRENT_TIMESTAMP + 1)
FROM YourTable AS a
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)