Date functions

Hello,
I need help with my function. I need a date difference (actual datetime and
another datetime in db) in hours It worked perfectly with DATEDIFF...But
now I found out that I should count just the "working "days (Mo-Fri). is
there any function or mechanism how to do the same but just with the
MONDAY-FRIDAY?
Thanks so much in advance
ZThe best approach is to use a calendar table that list the wends &
working days. Another alternative is to use a logic in your SELECT statement
that eliminates the wends ( CASE expressions, eliminate in the WHERE/
HAVING clause etc. ). If you post your table structures, sample data &
expected data ( www.aspfaq.com/5006 ), someone here might show you how you
can do this.
Anith|||Maybe something like:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// Returns the number of days between two dates.
/// </summary>
/// <param name="start">Start date.</param>
/// <param name="end">End date.</param>
/// <param name="includeWends">Include wends in count.</param>
/// <returns>Number of days.</returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static int DaysBetween(DateTime start, DateTime end, bool
includeWends)
{
start = start.Date;
end = end.Date;
if (start > end)
throw new ArgumentException("end date must be > start date.");
if (start == end)
return 0;
int days = ((TimeSpan)(end - start)).Days;
if (includeWends)
return days;
int bizDays = 0;
for (int i = 0; i < days; i++)
{
start = start.AddDays(1);
if (start.DayOfW == DayOfW.Saturday || start.DayOfW ==
DayOfW.Sunday)
continue;
bizDays++;
}
return bizDays;
}
};
Test
----
select dbo.DaysBetween('1/1/2005','1/3/2005', 0)
--
William Stacey [MVP]
"Zuska" <Zuska@.discussions.microsoft.com> wrote in message
news:16DBB07D-9498-4D2F-8899-6A113793BFB3@.microsoft.com...
| Hello,
| I need help with my function. I need a date difference (actual datetime
and
| another datetime in db) in hours It worked perfectly with DATEDIFF...But
| now I found out that I should count just the "working "days (Mo-Fri). is
| there any function or mechanism how to do the same but just with the
| MONDAY-FRIDAY?
|
| Thanks so much in advance
| Z
|
||||Zuska,
are you saying there are no holidays at all and every Monday is a
working day?|||"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1138652396.929823.234190@.g43g2000cwa.googlegroups.com...
> Zuska,
> are you saying there are no holidays at all and every Monday is a
> working day?
Every Monday should be a holiday. :-)|||HI,
youre right, there are more holidays then just sat or sun...it meansI should
make kind of calendar table to set all the holidays in it?
my table is easy
CREATE TABLE [dbo].[test] (
[ID] [int] NOT NULL ,
[YEAR] [int] NOT NULL ,
[TIME] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
There is a time of receiving sample in the column TIME and there is a
72hours guarantee time between time of receiving and actual time. I did it
using DATEDIFF...but it supposed to be 72 hours just in workingdays. So, if
the time in the table test on thursday 8:00AM the guarantee time ends on
tuesday 8:00AM.
thanks
Alexander Kuznetsov p_?e:

> Zuska,
> are you saying there are no holidays at all and every Monday is a
> working day?
>|||Zuska,
Once I was speeding up a system in which expressions like "2 business
days later" needed to be calculated real quick. My approach was quite
simple:
CREATE TABLE [dbo].[test] (
[ID] [int] NOT NULL ,
[WorkDayNum] int,
[date] [datetime] NOT NULL
) ON [PRIMARY]
for instance, days around Martin Luther King day would be represented
like this:
insert into test values(111, 86, '20060113')
-- Saturday
insert into test values(112, NULL, '20060114')
-- Sunday
insert into test values(113, NULL, '20060115')
-- Martin Luther King day
insert into test values(114, NULL, '20060116')
-- normal work day
insert into test values(114, NULL, '20060117')
So, calculating business days between 2 days is easy
select t2.workdaynum - t1.workdaynum
from test t1, test t1
where t1.[date] = '20051227'
and t2.[date]='20060114'
Makes sense?|||Alex, thanks for your help, youre the best:))its really nice and easy:))
Alexander Kuznetsov p_?e:

> Zuska,
> Once I was speeding up a system in which expressions like "2 business
> days later" needed to be calculated real quick. My approach was quite
> simple:
> CREATE TABLE [dbo].[test] (
> [ID] [int] NOT NULL ,
> [WorkDayNum] int,
> [date] [datetime] NOT NULL
> ) ON [PRIMARY]
> for instance, days around Martin Luther King day would be represented
> like this:
> insert into test values(111, 86, '20060113')
> -- Saturday
> insert into test values(112, NULL, '20060114')
> -- Sunday
> insert into test values(113, NULL, '20060115')
> -- Martin Luther King day
> insert into test values(114, NULL, '20060116')
> -- normal work day
> insert into test values(114, NULL, '20060117')
> So, calculating business days between 2 days is easy
> select t2.workdaynum - t1.workdaynum
> from test t1, test t1
> where t1.[date] = '20051227'
> and t2.[date]='20060114'
> Makes sense?
>