27,579
社区成员
发帖
与我相关
我的任务
分享
use test
go
if object_id('t1') is not null
drop table t1
go
create table t1 (id int,[姓名] varchar(10),[时间] datetime )
go
insert t1
select 1,'张三' ,'2011-08-18 15:47' union all
select 2,'李四' ,'2011-08-19 19:47' union all
select 3,'王五' ,'2011-08-20 20:47' union all
select 4,'麻子' ,'2011-08-21 22:47'
go
select o.*,[处理时间]=isnull(u.[处理时间],'') from t1 as o
left join
(select a.id,
cast(datediff(hh,b.[时间],a.[时间]) /24 as varchar) +'天'+
cast(datediff(hh,b.[时间],a.[时间]) %24 as varchar) +'小时' as [处理时间]
from t1 a,t1 b
where a.id-b.id=1 and a.id>1) as u
on o.id=u.id
/*
(所影响的行数为 4 行)
id 姓名 时间 处理时间
--- --- --- --------
1 张三 2011-08-18 15:47:00.000
2 李四 2011-08-19 19:47:00.000 1天4小时
3 王五 2011-08-20 20:47:00.000 1天1小时
4 麻子 2011-08-21 22:47:00.000 1天2小时
*/
go
drop table t1
CREATE TABLE TB (id INT IDENTITY(1,1),[name] VARCHAR(20),[time] VARCHAR(20))
INSERT INTO dbo.TB
SELECT '张三', '2011/8/18 15:47' UNION ALL
SELECT '李四', '2011/8/19 19:47' UNION ALL
SELECT '王五', '2011/8/20 20:47' UNION ALL
SELECT '麻子', '2011/8/21 22:27'
SELECT a.* ,ISNULL(LTRIM(DATEDIFF(HOUR, b.time, a.time) / 24) + '天' +
LTRIM(DATEDIFF(HOUR, b.time,a.time) % 24) + '小時','') AS '處理時間'
FROM dbo.TB a
LEFT JOIN dbo.TB b ON a.id = b.id + 1
DROP TABLE dbo.TB
/*
id name time 處理時間
----------- -------------------- -------------------- ------------------------------
1 张三 2011/8/18 15:47
2 李四 2011/8/19 19:47 1天4小時
3 王五 2011/8/20 20:47 1天1小時
4 麻子 2011/8/21 22:27 1天2小時
(4 個資料列受到影響)
*/
private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
string dateDiff = "";
if (e.ColumnIndex == 2)
{
if (e.RowIndex >0)
{
DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex];
DataGridViewRow row2 = this.dataGridView1.Rows[e.RowIndex - 1];
if (row != null && row2!=null)
{
if (row.Cells["create_date"].Value.ToString() != "" && row.Cells["create_date"].Value!= null)
{
DateTime dt1=Convert.ToDateTime(row.Cells["create_date"].Value.ToString());
DateTime dt2=Convert.ToDateTime(row2.Cells["create_date"].Value.ToString());
TimeSpan ts1 = new TimeSpan(dt1.Ticks);
TimeSpan ts2 = new TimeSpan(dt2.Ticks);
TimeSpan ts = ts1.Subtract(ts2).Duration();
if (int.Parse(ts.Days.ToString()) > 0)
{
dateDiff += ts.Days.ToString() + "天";
}
if (int.Parse(ts.Hours.ToString()) > 0)
{
dateDiff += ts.Hours.ToString() + "小时";
}
e.Value = dt1.ToString() +" "+dateDiff;
}else
{
e.Value=row.Cells["create_date"].Value.ToString() ;
}
}
}
DataTable table=new DataTable();
//。。。。加入获取数据的代码
DataColumn col = new DataColumn("时间间隔", typeof(string));
table.Columns.Add(col);
for(int i=0;i<table.Rows.Count-1;i++)
{
DateTime dt1 = DateTime.Parse(table.Rows[i][2].ToString());
DateTime dt2 = DateTime.Parse(table.Rows[i+1][2].ToString());
TimeSpan span = dt2 - dt1;
string str = string.Format("{0}{1}",span.Days,span.Hours);
table.Rows[i + 1][3] = str; //注意正确列索引!
}