469,923 Members | 1,432 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,923 developers. It's quick & easy.

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
Jul 20 '05 #1
6 5631
[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
Jul 20 '05 #2
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!
Jul 20 '05 #3
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!
Jul 20 '05 #4
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
Jul 20 '05 #5
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!
Jul 20 '05 #6
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
Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Able | last post: by
5 posts views Thread by kpp9c | last post: by
sunbin
4 posts views Thread by sunbin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.