Returning recordset that includes a DateDiff field in hh:mm:ss format?

Hi, not too swift with anything other than simple SQL statements, so
I'm looking for some help.

Using SQL Server 2000 with this stored proc:

(@.varCust varchar(50))

AS
SET NOCOUNT ON

SELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station,
d.WIdoc,
d.Start, d.StartUser, d.Finish, d.FinishUser
FROM tblWorkOrder w, tblDocs d
WHERE w.WorkOrder IS NULL AND w.WorkOrder = d.WorkOrder AND
d.Customer = @.varCust
GO

I'm trying to get a complete dataset so I can simply apply it as the
datasource to a datagrid in asp.net. I need to include a 'TimeSpan'
column that is the difference between d.Start and d.Finish. I also
need it to present in hh:mm:ss format in the datagrid column. (A) is
it possible to do this within the stored proc, and (B) how would "I"
do that?

Thanks!
Kathy[posted and mailed, please reply in news]

KathyB (KathyBurke40@.attbi.com) writes:
> SELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station,
> d.WIdoc,
> d.Start, d.StartUser, d.Finish, d.FinishUser
> FROM tblWorkOrder w, tblDocs d
> WHERE w.WorkOrder IS NULL AND w.WorkOrder = d.WorkOrder AND
> d.Customer = @.varCust
> GO
> I'm trying to get a complete dataset so I can simply apply it as the
> datasource to a datagrid in asp.net. I need to include a 'TimeSpan'
> column that is the difference between d.Start and d.Finish. I also
> need it to present in hh:mm:ss format in the datagrid column. (A) is
> it possible to do this within the stored proc, and (B) how would "I"
> do that?

See this example:

declare @.a datetime, @.b datetime
select @.a = '2003-12-23 10:55:12',
@.b = '2003-12-23 21:45:09'
select convert(char(10),
dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.

Would you please tell me what this line does:

dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

Also, not to be totally gready, but how do I fit this into my return
dataset as a column?

thanks again for answering.

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Erland, this works GREAT!!!! Thanks so much and happy holidays!

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Kathy Burke (kathyburke40@.attbi.com) writes:
> Would you please tell me what this line does:
> dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

The complete expression was:

select convert(char(10),
dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

I encourage you look up the convert, dateadd and datediff cuntions
in Books Online. You find Convert under the top Cast and Convert.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, I did look it up but still didn't understand the 19000101 (other
than it is the date 01/01/1900). I found that the 108 is the format
code, etc.

Thanks again...it saves me a lot of time doing this is the query
results.

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Kathy Burke (kathyburke40@.attbi.com) writes:
> Erland, I did look it up but still didn't understand the 19000101 (other
> than it is the date 01/01/1900). I found that the 108 is the format
> code, etc.

You can replace 1900-01-01 with any date. I could also have left an
empty string - which would be the same as 1900-01-01 thanks to the
default rules for datetime literals. The important for the example is
that we use a datetime value of which the time portion is midnight.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp