Converting Access qrys with logical expressions

I'm upsizing an Access database. Got the data converted, working on
the front end, converting queries to views, but having trouble
converting queries that use logical expressions like the following:
SELECT OrderId,
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.ClientId = Items.ClientId)
AND (OrderDetails.ItemId = Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2
AND ExpressBackorder =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBackOrderItems that I can use in further joins?

Thanks in anticipation
Terry Bell"Terry Bell" <dreadnought8@.hotmail.com> wrote in message
news:923537d6.0409142346.301c9c3@.posting.google.co m...
> I'm upsizing an Access database. Got the data converted, working on
> the front end, converting queries to views, but having trouble
> converting queries that use logical expressions like the following:
> SELECT OrderId,
> Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
> FROM OrderDetails
> INNER JOIN Items
> ON (OrderDetails.ClientId = Items.ClientId)
> AND (OrderDetails.ItemId = Items.ItemId)
> WHERE (NOT (SitesCustomerTypeId = 2
> AND ExpressBackorder =True
> AND OrderUrgency = 1 ))
> GROUP BY OrderId;
> Can someone suggest a strategy to achieve the same result, ie
> OrderId,ReadBackOrderItems that I can use in further joins?
> Thanks in anticipation
> Terry Bell

Are you asking how to rewrite the SUM expression? I don't know exactly what
the syntax above means, so this is a guess:

sum(case when BackOrderQtyAvailable > 0 then BackOrderQtyAvailable else 0
end * -1)

If this is wrong, then I suggest you post CREATE TABLE and INSERT statements
to create your tables and populate some sample data, along with the result
you expect to see from your query.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<41480077$1_2@.news.bluewin.ch>...
> "Terry Bell" <dreadnought8@.hotmail.com> wrote in message
> news:923537d6.0409142346.301c9c3@.posting.google.co m...
> > I'm upsizing an Access database. Got the data converted, working on
> > the front end, converting queries to views, but having trouble
> > converting queries that use logical expressions like the following:
> > SELECT OrderId,
> > Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
> > FROM OrderDetails
> > INNER JOIN Items
> > ON (OrderDetails.ClientId = Items.ClientId)
> > AND (OrderDetails.ItemId = Items.ItemId)
> > WHERE (NOT (SitesCustomerTypeId = 2
> > AND ExpressBackorder =True
> > AND OrderUrgency = 1 ))
> > GROUP BY OrderId;
> > Can someone suggest a strategy to achieve the same result, ie
> > OrderId,ReadBackOrderItems that I can use in further joins?
> > Thanks in anticipation
> > Terry Bell
> Are you asking how to rewrite the SUM expression? I don't know exactly what
> the syntax above means, so this is a guess:
> sum(case when BackOrderQtyAvailable > 0 then BackOrderQtyAvailable else 0
> end * -1)
> If this is wrong, then I suggest you post CREATE TABLE and INSERT statements
> to create your tables and populate some sample data, along with the result
> you expect to see from your query.
> Simon

Thanks very much Simon you have given me the direction I needed.
For the record, here's my full converted code - with some side errors
fixed

SELECT Q845UndeliveredOrderDetails.OrderId, SUM(CASE WHEN
BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS ReadyBackOrderItems
FROM Q845UndeliveredOrderDetails INNER JOIN
Items ON (Q845UndeliveredOrderDetails.ClientId =
Items.ClientId) AND (Q845UndeliveredOrderDetails.ItemId =
Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2 AND ExpressBackorder = 1 AND
OrderUrgency = 1))
GROUP BY Q845UndeliveredOrderDetails.OrderId;

So:
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems ... in Access
SQL
becomes
SUM(CASE WHEN BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS
ReadyBackOrderItems ... in SQL

I also note that in Access you can say something like

WHERE IsBackOrder

and it evaluates IsBackOrder as a logical expression
whereas in sql server we need to say

WHERE IsBackorder = 1

Is that right?

Then I guess I need to think about NULL too ...

Also I notice in the query analyser it comes up with a message saying
it can't understand the CASE statement, but I can ignore that, can I,
as it seems to go ahead and execute the query anyway?

Once again thanks a million this has saved me lots of time

Terry Bell|||<snip
> I also note that in Access you can say something like
> WHERE IsBackOrder
> and it evaluates IsBackOrder as a logical expression
> whereas in sql server we need to say
> WHERE IsBackorder = 1
> Is that right?

Not quite - there is no Boolean data type in MSSQL, so how to evaluate
'true' or 'false' depends on the data type you've chosen. One common
solution is to use the bit data type, with 1 for true and 0 for false, in
which case your code above is correct (assuming true = 1).

> Then I guess I need to think about NULL too ...

Yes - this is one reason why you often see requests for DDL (CREATE TABLE
etc.), as this makes it clear which columns allow NULL and which don't.
Something that seems to work fine may fail when NULLs are involved, so you
need to code for them if the data model allows them.

> Also I notice in the query analyser it comes up with a message saying
> it can't understand the CASE statement, but I can ignore that, can I,
> as it seems to go ahead and execute the query anyway?

I have no idea without seeing the full error, but perhaps this is error 8153
"Warning: Null value is eliminated by an aggregate or other SET operation."?
If so, it's just a warning that the column you SUMmed on contains NULL data.

> Once again thanks a million this has saved me lots of time
> Terry Bell

You're welcome.

Simon|||Generally just copy and paste from Access to Query Analyser. Check the
query runs correctly and then add CREATE PROCEDURE blah blah to the top
and run. This turns the script in to a stored procedure and loads it in
to the current database.

You might want to move all the restrictions to the WHERE clause other
wise you can get some interesting results if you are not very careful.

Sum((BackOrderQtyAvailable)* -1)

(BackOrderQtyAvailable > 0)

Adrian

Terry Bell wrote:
> I'm upsizing an Access database. Got the data converted, working on
> the front end, converting queries to views, but having trouble
> converting queries that use logical expressions like the following:
> SELECT OrderId,
> Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
> FROM OrderDetails
> INNER JOIN Items
> ON (OrderDetails.ClientId = Items.ClientId)
> AND (OrderDetails.ItemId = Items.ItemId)
> WHERE (NOT (SitesCustomerTypeId = 2
> AND ExpressBackorder =True
> AND OrderUrgency = 1 ))
> GROUP BY OrderId;
> Can someone suggest a strategy to achieve the same result, ie
> OrderId,ReadBackOrderItems that I can use in further joins?
> Thanks in anticipation
> Terry Bell|||On Thu, 16 Sep 2004 17:27:35 +0200, Simon Hayes wrote:
>"Terry Bell" <dreadnought8@.hotmail.com> wrote:
>>
>> Also I notice in the query analyser it comes up with a message saying
>> it can't understand the CASE statement, but I can ignore that, can I,
>> as it seems to go ahead and execute the query anyway?
> I have no idea without seeing the full error, but perhaps this is error 8153
> "Warning: Null value is eliminated by an aggregate or other SET operation."?
> If so, it's just a warning that the column you SUMmed on contains NULL data.

I don't think it's a null error -- I think he was editing his query in MS
Access's query editor, in an ADP file, rather than using SQL Server's Query
Analyzer. I've gotten that error from MS Access myself.

As Terry said, Access goes ahead and executes it anyway. It just can't
parse it properly to represent it in the graphical query editor.

Converting a Hex string to binary

I am trying to write a function to convert a hex string to binary. I would like it in a function so I can use it on aggregate data in queries (instead of having to cursor through the data). So, I write my function:

CREATE FUNCTION HexToBinary (@.hexstring char(16)) RETURNS binary(8)
AS
BEGIN
declare @.b binary(8)
,@.sql nvarchar(255)

SET @.sql = N'SET @.b = 0x' + @.hexstring
EXEC sp_executesql @.sql,N'@.b binary(8) out',@.b output
RETURN @.b
END

Then, I try to call my function:

SELECT HexToBinary('E0')

...and I get:

Msg 195, Level 15, State 10, Line 1
'HexToBinary' is not a recognized built-in function name.

However, I can get it to work if I use a slightly different syntax:

declare @.b binary(8)
exec @.b = HexToBinary 'E0'
select @.b

Any thoughts as to what might be going on? Obviously, the lower syntax does not help me call this function in queries, which is really my goal.sp_executesql is indeterministic and is not allowed within a UDF.

Here is the UDF that would give you what you want (courtesy of Itzik):

create function dbo.fn_hexstrtovarbin(@.input varchar(8000))
returns varbinary(8000)
as
begin
declare @.result varbinary(8000), @.i int, @.l int

set @.result = 0x
set @.l = len(@.input)/2
set @.i = 2

while @.i <= @.l
begin
set @.result = @.result +
cast(cast(case lower(substring(@.input, @.i*2-1, 1))
when '0' then 0x00
when '1' then 0x10
when '2' then 0x20
when '3' then 0x30
when '4' then 0x40
when '5' then 0x50
when '6' then 0x60
when '7' then 0x70
when '8' then 0x80
when '9' then 0x90
when 'a' then 0xa0
when 'b' then 0xb0
when 'c' then 0xc0
when 'd' then 0xd0
when 'e' then 0xe0
when 'f' then 0xf0
end as tinyint) |
cast(case lower(substring(@.input, @.i*2, 1))
when '0' then 0x00
when '1' then 0x01
when '2' then 0x02
when '3' then 0x03
when '4' then 0x04
when '5' then 0x05
when '6' then 0x06
when '7' then 0x07
when '8' then 0x08
when '9' then 0x09
when 'a' then 0x0a
when 'b' then 0x0b
when 'c' then 0x0c
when 'd' then 0x0d
when 'e' then 0x0e
when 'f' then 0x0f
end as tinyint) as binary(1))
set @.i = @.i + 1
end

return @.result
end
go

Converting

Hi, can you help me converting some queries from ASP application Oracle to
SQL SERVER 2000?
example:
1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
hh24:mi:ss')"
2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
How can it be converted to SQL Server?Hi
You can use the convert function to format dates as text or cast to cast
between datatypes. If your datestring is in a "safe" format e.g CCYYMMDD or
'CCYY-MM-DDTHH:MM:SS.NNN then you could let it do an implicit conversion e.g
.
and p.DATACAD < '20071120'
If you specify a date with no time it will default to midnight 00:00:00.000
Datetime granularity is 3.33 milliseconds and smalldatetime is accurate to 1
minute.
You can try this out using Query Analyser or Management studio e.g.
SELECT CAST('11:31:08.000' AS datetime),
CAST('11:31:08.000' AS smalldatetime),
CAST('20071120' AS datetime),
CAST('20071120' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS datetime),
CAST('2007-11-20T23:59:59.990' AS datetime),
CAST('2007-11-20T23:59:59.992' AS datetime),
CAST('2007-11-20T23:59:59.993' AS datetime),
CAST('2007-11-20T23:59:59.994' AS datetime),
CAST('2007-11-20T23:59:59.996' AS datetime),
CAST('2007-11-20T23:59:59.997' AS datetime),
CAST('2007-11-20T23:59:59.998' AS datetime),
CAST('2007-11-20T23:59:59.999' AS datetime)
"Paulo" wrote:

> Hi, can you help me converting some queries from ASP application Oracle to
> SQL SERVER 2000?
> example:
> 1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
> hh24:mi:ss')"
> 2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
> How can it be converted to SQL Server?
>
>sqlsql

Converting

Hi, can you help me converting some queries from ASP application Oracle to
SQL SERVER 2000?
example:
1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
hh24:mi:ss')"
2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
How can it be converted to SQL Server?Hi
You can use the convert function to format dates as text or cast to cast
between datatypes. If your datestring is in a "safe" format e.g CCYYMMDD or
'CCYY-MM-DDTHH:MM:SS.NNN then you could let it do an implicit conversion e.g.
and p.DATACAD < '20071120'
If you specify a date with no time it will default to midnight 00:00:00.000
Datetime granularity is 3.33 milliseconds and smalldatetime is accurate to 1
minute.
You can try this out using Query Analyser or Management studio e.g.
SELECT CAST('11:31:08.000' AS datetime),
CAST('11:31:08.000' AS smalldatetime),
CAST('20071120' AS datetime),
CAST('20071120' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS datetime),
CAST('2007-11-20T23:59:59.990' AS datetime),
CAST('2007-11-20T23:59:59.992' AS datetime),
CAST('2007-11-20T23:59:59.993' AS datetime),
CAST('2007-11-20T23:59:59.994' AS datetime),
CAST('2007-11-20T23:59:59.996' AS datetime),
CAST('2007-11-20T23:59:59.997' AS datetime),
CAST('2007-11-20T23:59:59.998' AS datetime),
CAST('2007-11-20T23:59:59.999' AS datetime)
"Paulo" wrote:
> Hi, can you help me converting some queries from ASP application Oracle to
> SQL SERVER 2000?
> example:
> 1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
> hh24:mi:ss')"
> 2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
> How can it be converted to SQL Server?
>
>

Converting

Hi, can you help me converting some queries from ASP application Oracle to
SQL SERVER 2000?
example:
1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
hh24:mi:ss')"
2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
How can it be converted to SQL Server?
Hi
You can use the convert function to format dates as text or cast to cast
between datatypes. If your datestring is in a "safe" format e.g CCYYMMDD or
'CCYY-MM-DDTHH:MM:SS.NNN then you could let it do an implicit conversion e.g.
and p.DATACAD < '20071120'
If you specify a date with no time it will default to midnight 00:00:00.000
Datetime granularity is 3.33 milliseconds and smalldatetime is accurate to 1
minute.
You can try this out using Query Analyser or Management studio e.g.
SELECT CAST('11:31:08.000' AS datetime),
CAST('11:31:08.000' AS smalldatetime),
CAST('20071120' AS datetime),
CAST('20071120' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS datetime),
CAST('2007-11-20T23:59:59.990' AS datetime),
CAST('2007-11-20T23:59:59.992' AS datetime),
CAST('2007-11-20T23:59:59.993' AS datetime),
CAST('2007-11-20T23:59:59.994' AS datetime),
CAST('2007-11-20T23:59:59.996' AS datetime),
CAST('2007-11-20T23:59:59.997' AS datetime),
CAST('2007-11-20T23:59:59.998' AS datetime),
CAST('2007-11-20T23:59:59.999' AS datetime)
"Paulo" wrote:

> Hi, can you help me converting some queries from ASP application Oracle to
> SQL SERVER 2000?
> example:
> 1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
> hh24:mi:ss')"
> 2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
> How can it be converted to SQL Server?
>
>

convert subqueries to join

Hi all,
Recently saw some posts about how bad it is using subqueries (nested queries) and things like all the queries can be written with JOIN instead. However, for a query like the followings, I cannot figure out how to do it properly.

The data looks like the following, it contains 3 columns, id, which is the primary key of the table (unique), group, as which group this entry belongs to, and the article type, where 0 is not an article.

id grp article
1 1 0
2 1 0
3 1 0
4 1 3
5 1 2
6 1 0
7 2 0
8 2 3
9 2 1
10 2 0
11 2 0
12 2 0
13 3 5
14 3 2
15 3 0
16 3 0
17 3 1
18 3 1

What the query is trying to achieve is for each group, get the latest (maximum) id, and the article type (bear in mind "0" is not one of the allowed type). so for data above, the result will look something like this...

id grp article
5 1 2
9 2 1
18 3 1

The following is the query I come up with, the problem is, I don't know how to get the same result without using subquery. I don't even see how it is possible. Guys, please share some light with me, and educate me on this.

select a.id, a.grp, b.article
from
(
SELECT max(id) as id, grp
from list a
where article != 0
group by grp
) a
join list b
on a.id = b.id

thanks in advance!!!

Here it is,

Code Snippet

Create Table #data (

[id] int ,

[grp] int ,

[article] int

);

Insert Into #data Values('1','1','0');

Insert Into #data Values('2','1','0');

Insert Into #data Values('3','1','0');

Insert Into #data Values('4','1','3');

Insert Into #data Values('5','1','2');

Insert Into #data Values('6','1','0');

Insert Into #data Values('7','2','0');

Insert Into #data Values('8','2','3');

Insert Into #data Values('9','2','1');

Insert Into #data Values('10','2','0');

Insert Into #data Values('11','2','0');

Insert Into #data Values('12','2','0');

Insert Into #data Values('13','3','5');

Insert Into #data Values('14','3','2');

Insert Into #data Values('15','3','0');

Insert Into #data Values('16','3','0');

Insert Into #data Values('17','3','1');

Insert Into #data Values('18','3','1');

Code Snippet

Select data.* from #data data

inner join (Select Max(id) id, grp from #data group By grp) maxid

on maxid.id=data.id

On SQL Server 2005,

Code Snippet

;With CTE

as

(

Select data.*,Max(id) Over(partition by grp) maxid from #data data

)

Select id,grp,article from cte where id=maxid

|||Hi Manivannan,

thank you for the quick reply, one problem that I saw was you didn't check that article cannot be 0, but anyway, it doesn't matter.

I got a few questions tho. the first query that you posted is actually very similiar to the one I posted. You still use a nested query. the inter query identifies the max id of the entries grouped by the grp, then, join to the tabe (#data) to get the article#. The only difference I saw was that you put the inner query after the join statement. so instead of select * from a join b on a.id = b.id, you did something like select * from b join a on a.id = b.id

the second query, which is MSSQL 2005 specific, which to me, is still using a nested query. In there, you first specify CTE, then you do a select statement on top of it, which still look like a nested query to me.

Is it possible to use a straightforward query to achieve what I wanted to achieve? i.e. do just normal join with data, etc. because i reallly don't see how it can be done.

thanks so much about the answer tho, appreciated!
|||

Hi Ken,

For your requirement we have to use the subquery. Without subquery we can't achive this result.

In SQL server 2005, CTE is best option to use..

-Mani

|||

Do not get confused with "derived table" and "correlated subquery". Try to avoid the second one, if possible. Always test before deciding for the final approach.

The first one, the one you are using in your post, is executed once. The second one seems to be executed for every row in the outer reference, but it is better to verify the execution plan.

Examples:

select a.*

from list as a

where a.[id] = (select max([b.id]) from list as b where b.grp = a.grp)

go

select a.*

from list as a

where not exists (

select *

from list as b

where b.grp = a.grp and b.[id] > a.[id]

)

go

AMB

|||so, is

select a.*

from list as a

where a.[id] = (select max([b.id]) from list as b where b.grp = a.grp)

a correlated subquery?
because it looks to me for every row retrieved from list, this "(select max([b.id]) from list as b where b.grp = a.grp)" statement will be executed.

So I should execute the query the way I did (for SQL2000) and use CTE for SQL 2005? by the way, I tried the query in SQL 2005, and the execution time is reduced quite a lot. What made it so different to the subqueries I have?

somehow, the text looks different in the edit mode and browse mode >"<

|||

Can you change the font of your post, please?

I can not read it at all.

Thanks,

AMB

convert SQL Server DB to Access DB

Hi,
IS it possible to take a SQLServer DB and convert it into an access
version? If so, can you easily convert stored procedures in access
queries? Or is all this not possible?Using SSIS in SQL Server 2005 and using DTS in SQL Server 2000, you can
easily export your SQL Server tables and views into a Microsoft Access
database.
To do this, go to your database in SQL Server and right click on it.
Tasks\Export Data. Choose your source from SQL Server and choose your target
as Microsoft Access from the list and go on...
--
Ekrem Önsoy
"bcap" <rayh@.patriots.com> wrote in message
news:1190648932.092871.210280@.19g2000hsx.googlegroups.com...
> Hi,
> IS it possible to take a SQLServer DB and convert it into an access
> version? If so, can you easily convert stored procedures in access
> queries? Or is all this not possible?
>|||Hi
This is not the way most people go! Is SQL Express not an option? AFAIK
there is not a way to convert a stored procedure to an Access query. Your
stored procedure may contain code and logic that is not possible to do in an
Access query, so unless they are very simple single statements being run in
the query you will need to write the logic into the client application. You
can import/export the tables and data into Access quite easily.
John
"bcap" wrote:
> Hi,
> IS it possible to take a SQLServer DB and convert it into an access
> version? If so, can you easily convert stored procedures in access
> queries? Or is all this not possible?
>

Convert mssql file

I am running mssql server for a sharepoint site. I have a mssql .bak file
that needs to be converted to plain text sql queries.
Is it possible to convert it somehow?
You have to restore it (in SQL Server) and query its with SQL Server or pump
the data out to some more propetary format liek access.
HTH, Jens Suessmeyer.
"Nick Mirro" <dirdx@.comcast.net> schrieb im Newsbeitrag
news:utFWDpuVFHA.3488@.TK2MSFTNGP10.phx.gbl...
>I am running mssql server for a sharepoint site. I have a mssql .bak file
>that needs to be converted to plain text sql queries.
> Is it possible to convert it somehow?
>

Convert mssql file

I am running mssql server for a sharepoint site. I have a mssql .bak file
that needs to be converted to plain text sql queries.
Is it possible to convert it somehow?
You have to restore it (in SQL Server) and query its with SQL Server or pump
the data out to some more propetary format liek access.
HTH, Jens Suessmeyer.
"Nick Mirro" <dirdx@.comcast.net> schrieb im Newsbeitrag
news:utFWDpuVFHA.3488@.TK2MSFTNGP10.phx.gbl...
>I am running mssql server for a sharepoint site. I have a mssql .bak file
>that needs to be converted to plain text sql queries.
> Is it possible to convert it somehow?
>

Convert Milliseconds to HH:MM:SS

I am writing a report that Queries a SQL DB using 'SQL Server Business Intelligence Development Studio'. I have a field in the DB called duration and it is in milliseconds. I am trying to find an easy way to convert the format from Milliseconds to HH:MM:SS.

Nearest I can get is the following for the field:

=Int(((Fields!DURATION.Value/1000) / 60) / 60) & ":" & Int(((((Fields!DURATION.Value/1000) / 60) / 60) - Int(((Fields!DURATION.Value/1000) / 60) / 60)) * 60)

The output is in HH:MM. One issue with this is if the MM is say :03, it prints as :3. I lose the leading 0 so 9:03 (9hrs and 3 minutes) prints as 9:3. Where as 9:30 (9 hrs and 30 minutes) prints as 9:30 as it should.

Is there an easier way to do this?

TIA...

Mike...

Try using the following expression, which uses the TimeSpan struct.

=TimeSpan.FromMilliseconds(Fields!DURATION.Value).ToString()

Ian|||

This is the code I use, using a custom code function. hope it will help

J

Shared Function FormatTime(ByVal Seconds As Integer) As String
Dim str As String
Dim hour As Integer
Dim min As Integer
str = ""
hour = Abs(Seconds\3600)
min = Abs((Seconds MOD 3600)/60)
If Seconds>= 0
str = iif(hour <= 9, "0" & CStr(hour), FormatNumber(hour, 0, , ,TriState.True)) & ":" & iif(min <= 9, "0" & CStr(min), CStr(min))
Else
str = "-" & iif(hour <= 9, "0" & CStr(hour), FormatNumber(hour, 0, , ,TriState.True)) & ":" & iif(min <= 9, "0" & CStr(min), CStr(min))
End If
return str
End Function

use like this

=Code.FormatTime( Fields!yourfeildname.Value)

|||

this worked good except the seconds come out as a long decimal (09:11:04.982345). How do I round that off. doesn't have to be exact, just need to get rid of the stuff to the right of the decimal.

thx....

|||

You should just be able to format the output, or use the format command to convert the displayed output to whatever you require ( see BOL )

J

|||Try using FromSeconds and convert the milliseconds to seconds whole seconds. Examples,

Without Rounding:
=TimeSpan.FromMilliseconds(Int(Fields!DURATION.Value/1000)).ToString()

or

With Rounding:
=TimeSpan.FromMilliseconds(CInt(Fields!DURATION.Value/1000)).ToString()

Ian