比如说,判断一篇文章是多少时间前发表的,在程序里写了个函数:/// <summary>
/// 计算两个时间的时间间隔
/// </summary>
/// <param name="DateTimeOld">较早的日期和时间</param>
/// <param name="DateTimeNew">较后的日期和时间</param>
/// <returns></returns>
public static string DateDiff(DateTime DateTimeOld, DateTime DateTimeNew)
{
string dateDiff = "";
TimeSpan ts1 = new TimeSpan(DateTimeOld.Ticks);
TimeSpan ts2 = new TimeSpan(DateTimeNew.Ticks);
TimeSpan ts = ts1.Subtract(ts2).Duration();
int day = ts.Days;
int hou = ts.Hours;
int minu = ts.Minutes;
int sec = ts.Seconds;
if (day > 0)
{
if (day > 30)
{
if (day > 364)
{
dateDiff += day /365 + "年";
}
else
{
dateDiff += day/30 + "个月";
}
}
else
{
dateDiff += day.ToString() + "天";
}
}
else
{
if (hou > 0)
{
dateDiff += hou.ToString() + "小时";
}
else
{
if (minu > 0)
{
dateDiff += minu.ToString() + "分钟";
}
else
{
if (sec > 0)
{
dateDiff += sec.ToString() + "秒";
}
else
{
dateDiff += "0秒";
}
}
}
}
if (DateTimeNew.CompareTo(DateTimeOld)>0)
{
dateDiff += "前";
}
else
{
dateDiff += "后";
}
return dateDiff;
}
//程序里调用 DateDiff('2010/06/25 11:17:12',DateTime.Now)大概就返回几分钟前,几小时前什么的,类似QQ空间最近访问。后来感觉不好,又在数据库里写了个标量值函数做比较sqlUSE [aaa]
GO
/****** Object: UserDefinedFunction [dbo].[DateDiff] Script Date: 06/25/2010 11:10:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[DateDiff] (@DateTimeOld datetime,@DateTimeNew datetime)
RETURNS nvarchar(50)
AS
BEGIN
declare @date int,@result nvarchar(50)
set @result=''
set @date=datediff(dd,@DateTimeOld,@DateTimeNew)
if @date>0
begin
if @date>30
begin
if @date>364
begin
set @result=@result+convert(nvarchar(50),@date/365)+'年'
end
else
begin
set @result=@result+convert(nvarchar(50),@date/30)+'个月'
end
end
else
begin
set @result=@result+convert(nvarchar(50),@date)+'天'
end
end
else
begin
set @date=datediff(Hour,@DateTimeOld,@DateTimeNew)
if @date>0
begin
set @result=@result+convert(nvarchar(50),@date)+'小时'
end
else
begin
set @date=datediff(Minute,@DateTimeOld,@DateTimeNew)
if @date>0
begin
set @result=@result+convert(nvarchar(50),@date)+'分钟'
end
else
begin
set @date=datediff(Second,@DateTimeOld,@DateTimeNew)
if @date>0
begin
set @result=@result+convert(nvarchar(50),@date)+'秒'
end
else
begin
set @result=@result+convert(nvarchar(50),@date)+'秒'
end
end
end
end
if datediff(Second,@DateTimeOld,@DateTimeNew)<0
begin
set @result=Replace(@result+'后','-','')
end
else
begin
set @result=@result+'前'
end
return @result
END
sql语句调用 select [dbo].[DateDiff]('2010/06/25 11:17:12',GETDATE()),* from 表其实主要是感觉写的不好,希望大家帮忙改下,另外说说二种方法的效率,哪种好点
/// 计算两个时间的时间间隔
/// </summary>
/// <param name="DateTimeOld">较早的日期和时间</param>
/// <param name="DateTimeNew">较后的日期和时间</param>
/// <returns></returns>
public static string DateDiff(DateTime DateTimeOld, DateTime DateTimeNew)
{
string dateDiff = "";
TimeSpan ts1 = new TimeSpan(DateTimeOld.Ticks);
TimeSpan ts2 = new TimeSpan(DateTimeNew.Ticks);
TimeSpan ts = ts1.Subtract(ts2).Duration();
int day = ts.Days;
int hou = ts.Hours;
int minu = ts.Minutes;
int sec = ts.Seconds;
if (day > 0)
{
if (day > 30)
{
if (day > 364)
{
dateDiff += day /365 + "年";
}
else
{
dateDiff += day/30 + "个月";
}
}
else
{
dateDiff += day.ToString() + "天";
}
}
else
{
if (hou > 0)
{
dateDiff += hou.ToString() + "小时";
}
else
{
if (minu > 0)
{
dateDiff += minu.ToString() + "分钟";
}
else
{
if (sec > 0)
{
dateDiff += sec.ToString() + "秒";
}
else
{
dateDiff += "0秒";
}
}
}
}
if (DateTimeNew.CompareTo(DateTimeOld)>0)
{
dateDiff += "前";
}
else
{
dateDiff += "后";
}
return dateDiff;
}
//程序里调用 DateDiff('2010/06/25 11:17:12',DateTime.Now)大概就返回几分钟前,几小时前什么的,类似QQ空间最近访问。后来感觉不好,又在数据库里写了个标量值函数做比较sqlUSE [aaa]
GO
/****** Object: UserDefinedFunction [dbo].[DateDiff] Script Date: 06/25/2010 11:10:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[DateDiff] (@DateTimeOld datetime,@DateTimeNew datetime)
RETURNS nvarchar(50)
AS
BEGIN
declare @date int,@result nvarchar(50)
set @result=''
set @date=datediff(dd,@DateTimeOld,@DateTimeNew)
if @date>0
begin
if @date>30
begin
if @date>364
begin
set @result=@result+convert(nvarchar(50),@date/365)+'年'
end
else
begin
set @result=@result+convert(nvarchar(50),@date/30)+'个月'
end
end
else
begin
set @result=@result+convert(nvarchar(50),@date)+'天'
end
end
else
begin
set @date=datediff(Hour,@DateTimeOld,@DateTimeNew)
if @date>0
begin
set @result=@result+convert(nvarchar(50),@date)+'小时'
end
else
begin
set @date=datediff(Minute,@DateTimeOld,@DateTimeNew)
if @date>0
begin
set @result=@result+convert(nvarchar(50),@date)+'分钟'
end
else
begin
set @date=datediff(Second,@DateTimeOld,@DateTimeNew)
if @date>0
begin
set @result=@result+convert(nvarchar(50),@date)+'秒'
end
else
begin
set @result=@result+convert(nvarchar(50),@date)+'秒'
end
end
end
end
if datediff(Second,@DateTimeOld,@DateTimeNew)<0
begin
set @result=Replace(@result+'后','-','')
end
else
begin
set @result=@result+'前'
end
return @result
END
sql语句调用 select [dbo].[DateDiff]('2010/06/25 11:17:12',GETDATE()),* from 表其实主要是感觉写的不好,希望大家帮忙改下,另外说说二种方法的效率,哪种好点
如果你的数据库服务比较繁忙,还是放到CS中处理较好。数据库的任务主要是保存数据/查询数据计算的事,能放在CS中尽量放到CS中。