How to capture What time zone by DB Server is using?

I need to capture the time zone of the DB server and adjust some date columns in some of my tables to make sure all the dates are using PST.

My database is distributed into different smaller databases (example into laptops, PDAs etc..) around the world.

End of day I will sync all the databases in PST time zone. Is there a command in SQL to capture the timezone.

Thanks

The query below will give the offset:

select datediff(hour, GETUTCDATE(), CURRENT_TIMESTAMP)

You can map this to a table that contains the various timezones (pre-defined) and use it in your queries.

|||

Is there any way to know if the timezone supports daylight saving time? This gets the current offset, but not the timezone meta data.

|||

Thanks for the quick response. This will help me to start working on my code.

Thanks for your time.

|||Not without reading the registry or writing extended SP or calling some OS utility or SQLCLR code. But it is very easy to build a TimeZone dimension/table that contains all the predefined offsets and attributes. You can then use the obtained offset to query the TimeZone table to get the rest of the details.|||That's what I thought. Thanks!

How to calculate the difference between two dates - aging

I have created a Model and using Report Builder to create a report. The
only thing I cannot get is the formula for the date difference. I need to
take an [Opened Date and Time] and the [Closed Date and Time] and get the
difference in DD:HH:MM.
Thanks in advance!Check this:
http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx
Cheers,
MB
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!|||Hi
Take a look at DATEDIFF system function in the BOL.
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!|||The DATEDIFF function can give you the difference in minutes.
Expressing that in the form DD:HH:MM is not so simple, as there is
nothing in SQL Server in that format. If all you need to do is
display it you could turn it into a string.
Calculating the three parts is not as simple as using datepart three
times. It requires a bit of arithmetic.
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
To put this into the DD:HH:MM format we could use something like:
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT CONVERT(varchar(6),DATEDIFF(minute,@.from, @.to) / (60 * 24))
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) / 60) %
24)+100,2)
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) %
60))+100,2)
--
119:02:30
The trick used to add the leading zeroes was to add 100 before
converting to a string and taking the two rightmost characters.
Hopefully that gives you something to start with.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 00:12:50 -0500, "Cliff Parker"
<cliff.parker@.stewart.com> wrote:
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
>Thanks in advance!|||On Wed, 21 Mar 2007 08:02:47 -0400, Roy Harvey wrote:
>The DATEDIFF function can give you the difference in minutes.
>Expressing that in the form DD:HH:MM is not so simple, as there is
>nothing in SQL Server in that format. If all you need to do is
>display it you could turn it into a string.
>Calculating the three parts is not as simple as using datepart three
>times. It requires a bit of arithmetic.
(snip)
Hi Roy (and Cliff),
Well, for the HH:MM part, there is an alternative: compute the
difference in minutes, add that to a starting date (any date will do) at
midnight, and convert that to string using a "time only" format:
DECLARE @.from datetime;
DECLARE @.to datetime;
SET @.from = '20060704 8:00';
SET @.to = '20061031 10:30';
SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
+ CONVERT(char(5),
DATEADD(minute,
DATEDIFF(minute, @.from, @.to),
'19000101'), -- Any date will do
108);
Results in
119:02:30
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
> + CONVERT(char(5),
> DATEADD(minute,
> DATEDIFF(minute, @.from, @.to),
> '19000101'), -- Any date will do
> 108);
Using DATEDIFF for calculating days is not always correct depending on
the times of day of the two datetimes. Try it for times on either
side of midnight, such as
SET @.from = '20060704 21:00';
SET @.to = '20060705 01:30';
and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
day calculation as:
DATEDIFF(minute,@.from, @.to) / (60 * 24)
Roy Harvey
Beacon Falls, CT|||On Wed, 21 Mar 2007 17:54:53 -0400, Roy Harvey wrote:
>On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
>> + CONVERT(char(5),
>> DATEADD(minute,
>> DATEDIFF(minute, @.from, @.to),
>> '19000101'), -- Any date will do
>> 108);
>Using DATEDIFF for calculating days is not always correct depending on
>the times of day of the two datetimes. Try it for times on either
>side of midnight, such as
>SET @.from = '20060704 21:00';
>SET @.to = '20060705 01:30';
>and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
>day calculation as:
> DATEDIFF(minute,@.from, @.to) / (60 * 24)
Hi Roy,
That was a stupid error - thanks for catching it!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Roy,
Thanks for the information. It was right on the money. You have helped me
out big time!
Many cudos!
Cliff Parker

How to calculate the difference between two dates - aging

I have created a Model and using Report Builder to create a report. The
only thing I cannot get is the formula for the date difference. I need to
take an [Opened Date and Time] and the [Closed Date and Time] and ge
t the
difference in DD:HH:MM.
Thanks in advance!Check this:
http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx
Cheers,
MB
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and g
et the
>difference in DD:HH:MM.
> Thanks in advance!|||Hi
Take a look at DATEDIFF system function in the BOL.
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and g
et the
>difference in DD:HH:MM.
> Thanks in advance!|||The DATEDIFF function can give you the difference in minutes.
Expressing that in the form DD:HH:MM is not so simple, as there is
nothing in SQL Server in that format. If all you need to do is
display it you could turn it into a string.
Calculating the three parts is not as simple as using datepart three
times. It requires a bit of arithmetic.
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
To put this into the DD:HH:MM format we could use something like:
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT CONVERT(varchar(6),DATEDIFF(minute,@.from
, @.to) / (60 * 24))
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minut
e,@.from, @.to) / 60) %
24)+100,2)
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minut
e,@.from, @.to) %
60))+100,2)
119:02:30
The trick used to add the leading zeroes was to add 100 before
converting to a string and taking the two rightmost characters.
Hopefully that gives you something to start with.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 00:12:50 -0500, "Cliff Parker"
<cliff.parker@.stewart.com> wrote:

>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and g
et the
>difference in DD:HH:MM.
>Thanks in advance!|||On Wed, 21 Mar 2007 08:02:47 -0400, Roy Harvey wrote:

>The DATEDIFF function can give you the difference in minutes.
>Expressing that in the form DD:HH:MM is not so simple, as there is
>nothing in SQL Server in that format. If all you need to do is
>display it you could turn it into a string.
>Calculating the three parts is not as simple as using datepart three
>times. It requires a bit of arithmetic.
(snip)
Hi Roy (and Cliff),
Well, for the HH:MM part, there is an alternative: compute the
difference in minutes, add that to a starting date (any date will do) at
midnight, and convert that to string using a "time only" format:
DECLARE @.from datetime;
DECLARE @.to datetime;
SET @.from = '20060704 8:00';
SET @.to = '20061031 10:30';
SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
+ CONVERT(char(5),
DATEADD(minute,
DATEDIFF(minute, @.from, @.to),
'19000101'), -- Any date will do
108);
Results in
119:02:30
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
> + CONVERT(char(5),
> DATEADD(minute,
> DATEDIFF(minute, @.from, @.to),
> '19000101'), -- Any date will do
> 108);
Using DATEDIFF for calculating days is not always correct depending on
the times of day of the two datetimes. Try it for times on either
side of midnight, such as
SET @.from = '20060704 21:00';
SET @.to = '20060705 01:30';
and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
day calculation as:
DATEDIFF(minute,@.from, @.to) / (60 * 24)
Roy Harvey
Beacon Falls, CT|||On Wed, 21 Mar 2007 17:54:53 -0400, Roy Harvey wrote:

>On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>
>Using DATEDIFF for calculating days is not always correct depending on
>the times of day of the two datetimes. Try it for times on either
>side of midnight, such as
>SET @.from = '20060704 21:00';
>SET @.to = '20060705 01:30';
>and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
>day calculation as:
> DATEDIFF(minute,@.from, @.to) / (60 * 24)
Hi Roy,
That was a stupid error - thanks for catching it!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Roy,
Thanks for the information. It was right on the money. You have helped me
out big time!
Many cudos!
Cliff Parker

How to calculate the difference between two dates - aging

I have created a Model and using Report Builder to create a report. The
only thing I cannot get is the formula for the date difference. I need to
take an [Opened Date and Time] and the [Closed Date and Time] and get the
difference in DD:HH:MM.
Thanks in advance!
Check this:
http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx
Cheers,
MB
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!
|||Hi
Take a look at DATEDIFF system function in the BOL.
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!
|||The DATEDIFF function can give you the difference in minutes.
Expressing that in the form DD:HH:MM is not so simple, as there is
nothing in SQL Server in that format. If all you need to do is
display it you could turn it into a string.
Calculating the three parts is not as simple as using datepart three
times. It requires a bit of arithmetic.
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
To put this into the DD:HH:MM format we could use something like:
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT CONVERT(varchar(6),DATEDIFF(minute,@.from, @.to) / (60 * 24))
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) / 60) %
24)+100,2)
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) %
60))+100,2)
119:02:30
The trick used to add the leading zeroes was to add 100 before
converting to a string and taking the two rightmost characters.
Hopefully that gives you something to start with.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 00:12:50 -0500, "Cliff Parker"
<cliff.parker@.stewart.com> wrote:

>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
>Thanks in advance!
|||On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
> + CONVERT(char(5),
> DATEADD(minute,
> DATEDIFF(minute, @.from, @.to),
> '19000101'), -- Any date will do
> 108);
Using DATEDIFF for calculating days is not always correct depending on
the times of day of the two datetimes. Try it for times on either
side of midnight, such as
SET @.from = '20060704 21:00';
SET @.to = '20060705 01:30';
and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
day calculation as:
DATEDIFF(minute,@.from, @.to) / (60 * 24)
Roy Harvey
Beacon Falls, CT
|||Roy,
Thanks for the information. It was right on the money. You have helped me
out big time!
Many cudos!
Cliff Parker

how to calculate hours between 2 dates but excluding weekends ?

Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened
and Ticket.solved_date) but excluding weekends ?
This function calculates hours only
DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
Thanks!No easy way! I have to do something like this myself next week.
You have to decide whether this is going to be done in SQL or VB. I'd
suggest VB will be the easiest to code and depending on the volumes of
data involved may be best.
So assuming VB, I'd suggest the datediff function you have, then work
out how many weekends in the daterange, you can use the weekday
function to tell what day you are starting on, subtract the number of
weekends * 48 and you'll be someway towards it.
--
Regards
Chris
agenda9533 wrote:
> Looking for way&logic to calculate hours between 2 dates
> (Ticket.date_opened and Ticket.solved_date) but excluding weekends ?
> This function calculates hours only
> DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> Thanks!|||See my reply to your previous post.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
news:5085DC03-FEE0-4934-9EFE-12BDE708F139@.microsoft.com...
> Looking for way&logic to calculate hours between 2 dates
> (Ticket.date_opened
> and Ticket.solved_date) but excluding weekends ?
> This function calculates hours only
> DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> Thanks!
>|||Perhaps some T-SQL?
I plan on trying this out when I get the time:
http://www.aspfaq.com/show.asp?id=2453
No holidays though!
agenda9533 wrote:
> Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened
> and Ticket.solved_date) but excluding weekends ?
> This function calculates hours only
> DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> Thanks!
>

How to calculate hours between 2 dates but excluding weekends ?

Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened
and Ticket.solved_date) but excluding weekends ?
This function calculates hours only
DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
Thanks!You need to create a custom VB.NET function that takes the two dates, sth
like:
Dim DaysPassed As Integer = DateDiff(DateInterval.Day, d1, d2)
Dim SaturdaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,
d1, d2, FirstDayOfWeek.Sunday) 'Using Sunday because it means that the
Saturday has actually fully passed by
Dim SundaysPassed As Integer = DateDiff(DateInterval.WeekOfYear, d1,
d2, FirstDayOfWeek.Monday)
Dim HoursPassed As Integer = (DaysPassed - SaturdaysPassed -
SundaysPassed) * 24
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
news:F4E3A740-68A1-4547-BE60-F034A964554F@.microsoft.com...
> Looking for way&logic to calculate hours between 2 dates
> (Ticket.date_opened
> and Ticket.solved_date) but excluding weekends ?
> This function calculates hours only
> DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> Thanks!
>|||Wow! Thanks a lot!
"Teo Lachev [MVP]" wrote:
> You need to create a custom VB.NET function that takes the two dates, sth
> like:
> Dim DaysPassed As Integer = DateDiff(DateInterval.Day, d1, d2)
> Dim SaturdaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,
> d1, d2, FirstDayOfWeek.Sunday) 'Using Sunday because it means that the
> Saturday has actually fully passed by
> Dim SundaysPassed As Integer = DateDiff(DateInterval.WeekOfYear, d1,
> d2, FirstDayOfWeek.Monday)
> Dim HoursPassed As Integer = (DaysPassed - SaturdaysPassed -
> SundaysPassed) * 24
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
> news:F4E3A740-68A1-4547-BE60-F034A964554F@.microsoft.com...
> > Looking for way&logic to calculate hours between 2 dates
> > (Ticket.date_opened
> > and Ticket.solved_date) but excluding weekends ?
> > This function calculates hours only
> > DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> >
> > Thanks!
> >
>
>|||How to declare function?
How to pass date_opened and last_updated values?
Should it be report fields - Fields!date_opened.Value,
Fields!last_updated.Value?
"Teo Lachev [MVP]" wrote:
> You need to create a custom VB.NET function that takes the two dates, sth
> like:
> Dim DaysPassed As Integer = DateDiff(DateInterval.Day, d1, d2)
> Dim SaturdaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,
> d1, d2, FirstDayOfWeek.Sunday) 'Using Sunday because it means that the
> Saturday has actually fully passed by
> Dim SundaysPassed As Integer = DateDiff(DateInterval.WeekOfYear, d1,
> d2, FirstDayOfWeek.Monday)
> Dim HoursPassed As Integer = (DaysPassed - SaturdaysPassed -
> SundaysPassed) * 24
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
> news:F4E3A740-68A1-4547-BE60-F034A964554F@.microsoft.com...
> > Looking for way&logic to calculate hours between 2 dates
> > (Ticket.date_opened
> > and Ticket.solved_date) but excluding weekends ?
> > This function calculates hours only
> > DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
> >
> > Thanks!
> >
>
>|||You create either embedded funtion in your report in VB.net or an external
assembly. From the field that needs to be validated, you pass the date
fields as input parameters. You may find the beginning of this article
helpful to get you started
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp).
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
news:9CA39B8B-FD4B-42C9-8C21-1B78168602DB@.microsoft.com...
> How to declare function?
> How to pass date_opened and last_updated values?
> Should it be report fields - Fields!date_opened.Value,
> Fields!last_updated.Value?
> "Teo Lachev [MVP]" wrote:
>> You need to create a custom VB.NET function that takes the two dates, sth
>> like:
>> Dim DaysPassed As Integer = DateDiff(DateInterval.Day, d1, d2)
>> Dim SaturdaysPassed As Integer =>> DateDiff(DateInterval.WeekOfYear,
>> d1, d2, FirstDayOfWeek.Sunday) 'Using Sunday because it means that the
>> Saturday has actually fully passed by
>> Dim SundaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,
>> d1,
>> d2, FirstDayOfWeek.Monday)
>> Dim HoursPassed As Integer = (DaysPassed - SaturdaysPassed -
>> SundaysPassed) * 24
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
>> news:F4E3A740-68A1-4547-BE60-F034A964554F@.microsoft.com...
>> > Looking for way&logic to calculate hours between 2 dates
>> > (Ticket.date_opened
>> > and Ticket.solved_date) but excluding weekends ?
>> > This function calculates hours only
>> > DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).
>> >
>> > Thanks!
>> >
>>

How to calculate hours between 2 dates but excluding weekends

Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened and Ticket.solved_date) but excluding weekends ?
This function calculates hours only
DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).

Thanks!I suggest you create a function to calculate the number of weekdays between 2 dates (excluding the weekends using the datepart(dw,@.date)-function) and multiplying this by 24...

or alternativly,
calculating the weekend-days, multiplying this by 24 and subtract the datediff-result with this value
this will only be correct if you use dates which are no weekenddays themselfs ofcourse...

How to calculate hours between 2 dates but excluding weekends

Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened and Ticket.solved_date) but excluding weekends ?
This function calculates hours only
DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).

Thanks!I suggest you create a function to calculate the number of weekdays between 2 dates (excluding the weekends using the datepart(dw,@.date)-function) and multiplying this by 24...

or alternativly,
calculating the weekend-days, multiplying this by 24 and subtract the datediff-result with this value
this will only be correct if you use dates which are no weekenddays themselfs ofcourse...

How to calculate a date difference in days

Suppose I have these two days fields
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM

How can i get the DateDifference of these two dates in days.

Use DateDiff(DateInterval.Day, Fields!Date1.Value, Fields!Date2.Value)

Where date1 is the start date and date2 is end date.

Shyam

|||

Hello Kamii,

If you're wanting to do this from your SQL query...

select datediff(d, ddold, ddnew)

If from Reporting Services, use this as your expression...

=DateDiff("d", Fields!ddold.Value, Fields!ddnew.Value)

Jarret

|||Can u please mark my post as answer?