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

[KathyB]

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

[Erland Sommarskog]

[posted and mailed, please reply in news]

KathyB (Ka**********@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, so****@algonet.se

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

[Kathy Burke]

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!

[Kathy Burke]

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!

[Erland Sommarskog]

Kathy Burke (ka**********@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, so****@algonet.se

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

[Kathy Burke]

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!

[Erland Sommarskog]

Kathy Burke (ka**********@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, so****@algonet.se

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