How to change date formats in stored procedure

I need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.
thanks
mikeLook up CAST AND CONVERT in Books Online. But be aware that this changes the datatype to a string, and should be used for output formatting only. And it is preferable to let your interface or reporting tool handle formatting of output.
Why do you think you need to convert it to short date? Are you trying to truncate the value?|||I am inserting a date value into a table and I dont want the timestamp portion included.|||Thanks! I figured it out using the convert function|||A very similar question was answered yesterday.|||Heck, cascred, this is one of those questions that gets asked every WEEK.

musicmikem, this is a more efficient method of truncating a datatime value, if less intuitive: dateadd(d, datediff(d, 0, [YourDate]), 0)|||Weekly? Hell sometimes it's hourly|||Weekly? Hell sometimes it's hourly Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.|||Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.

You make things so complicated. Why didn't you just say that today the question will be asked at:

create table Numlist (num int identity(1,1) not null primary key)
go
insert Numlist default values
while scope_identity() < 24 insert numlist default values
go
select dateadd(hh, num, '10/4/2005') from numlist
go
drop table numlist

Bill|||Because as any good DBA knows, that method requires a Brain Scan instead of a Clock Seek.|||I'm actually in favor of the simpler:SELECT DateAdd(hour, o0 + o1 * 8, dateadd(d, datediff(d, 0, GetDate()), 0))
FROM (SELECT 0 AS o1 UNION SELECT 1 UNION SELECT 2) AS a
CROSS JOIN (SELECT 0 AS o0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS bBonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).

-PatP|||Bonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).
-PatP

I like it. I have never seen this approach before.

You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.

Bill|||You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.Gold star!

Old Unix machines (especially the DEC ones) used to do nearly everything in octal. Three full octets (00-27 octal is 0-23 decimal) will exactly hold all of the hours in a day.

-PatP|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?

Hey. Be careful what you suggest. Things weren't pretty in the days before a relational DBMS came along. We used to do this stuff in COBOL ... without SQL! There are some scars, but not from hernias.|||try dis one.
select convert(varchar,datefield,101) from tablename

How to catch sql exceptions gracefully when deleting some records

I use the following function (in the BLL) to delete some records:

PublicFunction DeleteStep4Dashboards()AsBoolean

Try

adpDashboards.DeleteStep4Dashboards()

Catch exAs Exception

ReturnFalse

EndTry

ReturnTrue

EndFunction

How can I catch the sql database errors when deleting the records goes wrong.

You can add a Catch for a SqlException e.g.

Catch sqlExAs SqlException

|||

You can specify the type of the exception that you want to catch, e.g. Catch ex as SqlException (rather than as Exception).

However, catching exceptions and returning a value indicating success or failure is in general bad coding style. An exception means that something has gone wrong with what you are trying to do, and you either need to take some remedial action such as correcting parameters and retrying, or report it to the user and/or administrator.

Returning false or true puts the onus of detecting and handling errorconditions on all your method's callers, and there could be several ofthose scattered through your code, making it difficult to maintain. On the other hand, throwing an exception means that your callers only need to write code to handle the error condition if they are actually able to do something about it. Otherwise they need to be allowed to bubble up to the top layer so that they can be logged and/or the user can be notified that something went wrong.

|||

Another option is to put the delete into a stored procedure and handle it there -- in fact, you may be able to prevent any errors by checking the state of your data first to make sure the delete will work before actually trying it (eg, if a foreign key might prevent the delete)

|||

Is it really best practise to bubble up the error.

Can't I show an general error message to the user.

And handle the error (logging, e-mail to administrator) in the BLL?

|||

JohanNL:

Is it really best practise to bubble up the error.

Without meaning to answer for James, I would say that you be misundestanding what he is suggesting. As he wrote, the rule of thumb is to catch an exception only if you intend to do something about it, and that may mean logging the error and showing the user a general message. I would say that it's an application specific decision about where you want to log the errors, but if you think about it, it's not worth the effort to put try...catch everywhere if all you're going to do is log it and move on. OTOH, updates often cry out for special handling in the event of a sql error, especially since it's often helpful to catch an error right where it happens.

The best thing to do, of course, is to anticipate every possible error and code in such a way that you prevent them from happening. If you think you might get a divide by zero exception, for instance, do the check before attempting the division and give the user a message. I do this in stored procedures all the time. However, if you could think of everything you probably wouldn't need an exception log anyway<g>

How To Catch Output from SP / Function

I want to catch
the resultset from a stored procedure
or
a table output parameter from a stored procedure
or
a table output from a function
into
a varchar variable.
Any tip?
Not possible?
(it is for mailing the result from a query)
/k"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
You cannot have a table output parameter from a stored procedure

> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
Not Possible.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||You cannot do that.
You can catch the output from a SP into a #Table. Thats the max you can do.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
> I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||On Fri, 25 Feb 2005 09:35:12 +0100, kurt sune wrote:
(snip)
>Any tip?
>Not possible?
Hi Kurt,
Roji and Vinod already informed you that what you want is not possible.

>(it is for mailing the result from a query)
But if that's what you want, then you should check out xp_sendmail in
Books Online.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to capture result from READTEXT?

In the continuing saga of dealing with blobs . . .
READTEXT is a fine function, but useless by itself. I need to return the
results of READTEXT into a varchar column but have been unable to do so or
even find a similar example. Displaying the results in Query Analyzer is all
the example MS provides.
I need something like the following:
Select @.string = READTEXT tblFLC_Catalog.longDescription @.textptr
@.chunkindex @.chunksize
Any suggestions?
MichaelPlease don't start new threads, there is already a thread about this you
started half an hour ago.
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:DE98D97E-C3CF-44DB-8C13-1747CD3194EF@.microsoft.com...
> In the continuing saga of dealing with blobs . . .
> READTEXT is a fine function, but useless by itself. I need to return the
> results of READTEXT into a varchar column but have been unable to do so or
> even find a similar example. Displaying the results in Query Analyzer is
> all
> the example MS provides.
> I need something like the following:
> Select @.string = READTEXT tblFLC_Catalog.longDescription @.textptr
> @.chunkindex @.chunksize
> Any suggestions?
> Michael|||> READTEXT is a fine function, but useless by itself. I need to return the
> results of READTEXT into a varchar column
I'm not sure I understand this. What exactly are you trying to accomplish
(e.g. forget about READTEXT for a minute, what are you going to actually DO
with each of these chunks)?|||Snake wrote:
> In the continuing saga of dealing with blobs . . .
> READTEXT is a fine function, but useless by itself. I need to return
> the results of READTEXT into a varchar column but have been unable to
> do so or even find a similar example. Displaying the results in Query
> Analyzer is all the example MS provides.
> I need something like the following:
> Select @.string = READTEXT tblFLC_Catalog.longDescription @.textptr
> @.chunkindex @.chunksize
> Any suggestions?
> Michael
How about querying the column directly using a SELECT statement?
Declare @.MyText VARCHAR(1000)
Select
@.MyText = SUBSTRING(pr_info, 1, 100)
From
pubs.dbo.pub_info
Where
pub_id = '0736'
Select @.MyText
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Aaron,
If you really read my orignal posting you will see that while these postings
are related, this one is more specific and focused on how to use the
READTEXT. Since your own responses to each of my postings is different, at
least on some level even you see them differently.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Please don't start new threads, there is already a thread about this you
> started half an hour ago.
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:DE98D97E-C3CF-44DB-8C13-1747CD3194EF@.microsoft.com...
>
>|||David,
Thanks for your response. I believe I read in BOL that for ntext columns
over 4000 characters one must use the READTEXT function. My data is longer
than 4000, therefore I must use READTEXT to parse my data and return it to m
y
program in, say, 1k chunks. Is this an incorrect approach?
I am warry of the pubs examples and sample code because they always assume
the most simplistic situations and from my point-of-view ignore much of the
real-world. One must always read the associated text in BOL to find the
gotchas!
Have a very nice 3-day wend.
"David Gugick" wrote:

> Snake wrote:
> How about querying the column directly using a SELECT statement?
> Declare @.MyText VARCHAR(1000)
> Select
> @.MyText = SUBSTRING(pr_info, 1, 100)
> From
> pubs.dbo.pub_info
> Where
> pub_id = '0736'
> Select @.MyText
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Actually, my reply here was meant for the original thread, and applies
equally to both. They are still both dealing with how to get a chunk of
text from readtext into a varchar variable. There is no reason to carry on
multiple distinct conversations about the same thing.

> Aaron,
> If you really read my orignal posting you will see that while these
> postings
> are related, this one is more specific and focused on how to use the
> READTEXT. Since your own responses to each of my postings is different, at
> least on some level even you see them differently.|||> Thanks for your response. I believe I read in BOL that for ntext columns
> over 4000 characters one must use the READTEXT function. My data is
> longer
> than 4000, therefore I must use READTEXT to parse my data and return it to
> my
> program in, say, 1k chunks. Is this an incorrect approach?
Well, did you TRY using SUBSTRING? Did you try the code samples that have
been posted?
Also, why would you want to use 1k chunks? Didn't you say you wanted to
stuff it into an NVARCHAR variable or column? If so, then why not use 4k
chunks? And if all you are doing is returning the data to your program, why
not just say SELECT NTextColumn FROM table, the program shouldn't have
display limitations like Query Analyzer (8,192 characters).
A

how to call C/C++ DLL in stored Procedure?

How do I call a C/++ DLL in SQL Server stored procedure?
The C function has following prototype.
double function_name(char* x,int i)
ThanksRead about extended stored procedures...

How to call an image via code behind

I'm running ssrs 2005 and want to write a function in the code in the page
something like this:
Public Shared Function ShowImage(val as integer) AS Image
IF val = 4 Then
Return apply.png
ELSE
Return nothing
END IF
End Function
Where apply.png is an image in the report project folder (source=external)
How can I do this?
--
moondaddy@.newsgroup.nospamI found a solution.
Basically, if a condition is true then I want to display the image. so I
put the following iff function in the images visibility property's hidden
section.
=IIF(Fields!VP_qpcCap.Value = 4,False,True)
I suppose if I had more complex logic, I could run a function in the code
behind and have it return true or false. then call that function in the
visibility property's hidden section.
"moondaddy" <moondaddy@.newsgroup.nospam> wrote in message
news:%23MMR56TPIHA.4740@.TK2MSFTNGP02.phx.gbl...
> I'm running ssrs 2005 and want to write a function in the code in the page
> something like this:
> Public Shared Function ShowImage(val as integer) AS Image
> IF val = 4 Then
> Return apply.png
> ELSE
> Return nothing
> END IF
> End Function
> Where apply.png is an image in the report project folder (source=external)
> How can I do this?
>
> --
> moondaddy@.newsgroup.nospam
>|||On Dec 12, 11:44 pm, "moondaddy" <moonda...@.newsgroup.nospam> wrote:
> I found a solution.
> Basically, if a condition is true then I want to display the image. so I
> put the following iff function in the images visibility property's hidden
> section.
> =IIF(Fields!VP_qpcCap.Value = 4,False,True)
> I suppose if I had more complex logic, I could run a function in the code
> behind and have it return true or false. then call that function in the
> visibility property's hidden section.
> "moondaddy" <moonda...@.newsgroup.nospam> wrote in message
> news:%23MMR56TPIHA.4740@.TK2MSFTNGP02.phx.gbl...
>
> > I'm running ssrs 2005 and want to write a function in the code in the page
> > something like this:
> > Public Shared Function ShowImage(val as integer) AS Image
> > IF val = 4 Then
> > Return apply.png
> > ELSE
> > Return nothing
> > END IF
> > End Function
> > Where apply.png is an image in the report project folder (source=external)
> > How can I do this?
> > --
> > moonda...@.newsgroup.nospam- Hide quoted text -
> - Show quoted text -
1. Add an Image object to your Report
2. Set the Value expression to "=Code.ShowImage( Fields!
VP_qpcCap.Value )
3. Change the ShowImage function to return a String
4. Change the Return line to Return "apply.pjm" (put it in double
quotes)
-- Scott

How to call a stored procedure from a function

Hi
The idea is to generate the sequence for some of the fields in the table.
Since the identity property sets the sequence to only one of the field in
the table, decided to have a UDF that would create a new sequence value
generated for the fields.
But when the function is called i get the error
"Only functions and extended stored procedures can be executed from within a
function." Please suggest
The below table will hold the names of the fields that would require the
sequence to be generated and the last value generated updated by the stored
procedure.
CREATE table SEQ_GENERATOR_TBL
( seq_name varchar(50) not null,
last_value bigint default 0 not null);
GO
insert into SEQ_GENERATOR_TBL(seq_name)
values('SEQ_IS_GLOBAL_IDENTIFIER');
CREATE function SEQ_GENERATOR_FUNC
(@.p_seq_name varchar(50))
RETURNS bigint
AS
BEGIN
DECLARE @.ret_next_value bigint
SET @.ret_next_value = (select last_value+1 as next_value
from SEQ_GENERATOR_TBL
WHERE seq_name = @.p_seq_name);
EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
RETURN @.ret_next_value;
END
GO
CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
@.p_seq_name varchar(50),
@.p_curr_value bigint
AS
BEGIN
BEGIN TRANSACTION;
UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
WHERE seq_name = @.p_seq_name;
COMMIT TRANSACTION;
RETURN;
END
GOJP
Do you expect the same sequence as the IDENTITY property is set to? Have you
considered using computed column?
"JP" <JP@.discussions.microsoft.com> wrote in message
news:CD9841BA-3ED6-4140-BC3D-3588FC522226@.microsoft.com...
> Hi
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from within
> a
> function." Please suggest
> The below table will hold the names of the fields that would require the
> sequence to be generated and the last value generated updated by the
> stored
> procedure.
> CREATE table SEQ_GENERATOR_TBL
> ( seq_name varchar(50) not null,
> last_value bigint default 0 not null);
> GO
> insert into SEQ_GENERATOR_TBL(seq_name)
> values('SEQ_IS_GLOBAL_IDENTIFIER');
> CREATE function SEQ_GENERATOR_FUNC
> (@.p_seq_name varchar(50))
> RETURNS bigint
> AS
> BEGIN
> DECLARE @.ret_next_value bigint
> SET @.ret_next_value = (select last_value+1 as next_value
> from SEQ_GENERATOR_TBL
> WHERE seq_name = @.p_seq_name);
> EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
> RETURN @.ret_next_value;
> END
> GO
> CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
> @.p_seq_name varchar(50),
> @.p_curr_value bigint
> AS
> BEGIN
> BEGIN TRANSACTION;
> UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
> WHERE seq_name = @.p_seq_name;
> COMMIT TRANSACTION;
> RETURN;
> END
> GO
>|||Where do you want to show the data?
If you use reports do the numbering there
Madhivanan|||JP (JP@.discussions.microsoft.com) writes:
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from
> within a function." Please suggest
Rework and redesign. A function must not change database state, why
updates are not permitted, and neither calls to stored procedure as
they could do about anything.
Itzik Ben-Gan discussed a couple of solution in his column T-SQL Black
Belt in SQL Server Magazine a couple of issues back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to call a funtion within a dll from a stored procedure

hi all,
i want to call a function of an activex dll from a stored procedures, Can some tell me how i can do that . The activex dll i want to call has been built using Visual C++ 6.0 .
Regards,
ManpreetDont have any idea how to do that in SQL 2000, but that is definitely a feature present in SQL server Yukon|||You will have to create an extended stored procedure and add it to ss using sp_addextendedproc.

The following link will help:

link (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q190/9/87.asp&NoWebContent=1)

How to Call a function using OLE DB Command Transformation

Hello

i am trying to call a function from the SQL server using Ole DB command Transformation using [dbo].[ConvertToDate] ?,?,?,?

there are no errors while executing this transformation

but this function returns a value

Now i need to capture this value how do i do that using the OLE DB command Transformation or any other transformation

Thanks

Hello Please can somebody answer this question|||

I don't believe that the OLE DB Command transform can capture the return value from a scalar function. You can work around this by calling the function inside a stored procedure, and returning the function's return as an output parameter from the procedure.

There is an article that demonstrates this technique (output parameter handling, not function wrapping) here: http://wiki.sqlis.com/default.aspx/SQLISWiki/OLEDBCommandTransformationAndIdentityColumns.html?diff=y

How to Call a function using OLE DB Command Tranformation

Hello

i am trying to call a function from the SQL server using Ole DB command Transformation using [dbo].[ConvertToDate] ?,?,?,?

there are no errors while executing this transformation

but this function returns a value

Now i need to capture this value how do i do that using the OLE DB command Transformation or any other transformation

Thanks

Hello Please can somebody answer this question|||

I don't believe that the OLE DB Command transform can capture the return value from a scalar function. You can work around this by calling the function inside a stored procedure, and returning the function's return as an output parameter from the procedure.

There is an article that demonstrates this technique (output parameter handling, not function wrapping) here: http://wiki.sqlis.com/default.aspx/SQLISWiki/OLEDBCommandTransformationAndIdentityColumns.html?diff=y

How to call a function from a column formula in my MS SQL table

Good day!

What is the syntax on calling a function from a column formula in an MS SQL table.

I created a table, one column's value will be coming from a function. And at the same time, I will pass parameters to the function. How do I do this? Is this correct?

SELECT dbo.FunctionName([Parameter1, Parameter2])

But i can't save the table, "Error validating the formula".

Pls. help
Thanks a lot.<edit> Never mind, I misunderstood what you are doing.
I'm afraid I have no advice.|||It may or may not work depending on what you are trying to do. You can use a udf and define the result as simply:

DEFAULT (dbo.udfMyFunction('SomeParam','OtherParam'))

However, SomeParam and OtherParam must be constants or system functions (like suser_sname() or host_name()). They can't be names of columns in your table.

Regards,

hmscott|||Or perhaps he want a computed column:

create table foo (
id int,
hash as dbo.getHash(id),
...
);

How to calculate number of occurances of a character in a string

Is there a string function in T-sql which tells us that the number of occurances of a character in a string?
eg. abracadabra
no. of a's in the string : 5
Any Help is appreciated.select len(@.YourString)- len(replace(@.YourString, 'a', ''))

blindman

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 actual size of column?

Hi,
I am wondering if any equivalent function available which
gives the actual length of column in bytes as VSIZE in
Oracle?
If not how to calcuate actual column size?
Thanks in advance.
RajIs the DATALENGTH function what you're looking for? For example:
SELECT DATALENGTH(au_lname)
FROM pubs..authors
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Raj" <vmresumes@.yahoo.com> wrote in message
news:8fdb01c345a2$356250c0$a401280a@.phx.gbl...
> Hi,
> I am wondering if any equivalent function available which
> gives the actual length of column in bytes as VSIZE in
> Oracle?
> If not how to calcuate actual column size?
> Thanks in advance.
> Raj