sqlserver算月差,sql 算时间差

sql 计算两个日期相差多少年月日

如：起始时间

2011-1-1

截止日期

2011-2-1

两个时间比较相差

0年1个月

如：起始时间

2010-11-1

截止日期

2011-12-1

两个时间比较相差

1年1个月

怎么比较才能得出~

相差多少年

多少个月

这个当然很简单,

一句sql就可以了：select

ltrim(datediff(yy,@dt1,@dt2))+'年'+ltrim(datediff(mm,@dt1,@dt2)%12)+'月'

但是如果连几天也要计算呢？闲着没事自己随便在sql

server下写了写，store

procedures代码如下，应该没可以了：create

procedure

getdatediff

@fromdate

nvarchar(10),

@todate

nvarchar(10)

as

declare

@yeardiff

integer

declare

@monthdiff

integer

declare

@daydiff

integer

declare

@tomonth

integer

declare

@fromday

integer

declare

@today

integer

set

@yeardiff

=

ltrim(datediff(yy,

@fromdate,

@todate))

set

@monthdiff

=

ltrim(datediff(mm,

@fromdate,

@todate)%12)

set

@tomonth

=

month(cast(@todate

as

datetime))

set

@fromday

=

day(cast(@fromdate

as

datetime))

set

@today

=

day(cast(@todate

as

datetime))

if

(@today

-

@fromday)

begin

if

(@tomonth

-

1)

=

1

or

(@tomonth

-

1)

=

3

or

(@tomonth

-

1)

=

5

or

(@tomonth

-

1)

=

7

or

(@tomonth

-

1)

=

8

or

(@tomonth

-

1)

=

10

or

(@tomonth

-

1)

=

12

begin

set

@daydiff

=

31

+

@today

-

@fromday

set

@monthdiff

=

@monthdiff

-1

end

else

begin

set

@daydiff

=

30

+

@today

-

@fromday

set

@monthdiff

=

@monthdiff

-1

end

end

else

begin

set

@daydiff

=

@today

-

@fromday

end

select

cast(@yeardiff

as

nvarchar(10))

+

'年'

+

cast(@monthdiff

as

nvarchar(10))

+

'月'

+

cast(@daydiff

as

nvarchar(10))

+

'日'

相关问答

Q1: 如何用SQL计算access2007数据表两个日期差？

access与SqlServer 之时间与日期及其它SQL语句比较

1、Datediff：

1.1算出日期差：

1.access: datediff('d',fixdate,getdate())

2.sqlserver: datediff(day,fixdate,getdate())

ACCESS实例： select * from table where data=datediff('d',fixdate,getdate())

sqlserver实例： select * from table where data=datediff(day,fixdate,getdate())

1.2算出时间差：

1.access: datediff('h',fixdate,getdate())

2.sqlserver: datediff(Hour,'2004-12-10',getdate())

ACCESS实例： select DATEDIFF('h',HMD,getdate())

sqlserver实例： select datediff(Hour,'2004-12-10',getdate())

1.3算出月份差：

1.access: datediff('m',fixdate,getdate())

2.sqlserver: datediff(Month,'2004-12-10',getdate())

ACCESS实例： select DATEDIFF('m',HMD,getdate())

sqlserver实例： select datediff(Month,'2004-12-10',getdate())

Q2: sqlserver查询两个时间之间经历了哪几个月

select datediff(month,'2017-1-5','2018-05-25')/12 ,datediff(month,'2017-1-5','2018-05-25')%12

执行结果：1年4个月

注释：

datediff(month,'2017-1-5','2018-05-25')取出两个日期相差的总月数；

datediff(month,'2017-1-5','2018-05-25')/12 取出两个日期相差的总年数。

datediff(month,'2017-1-5','2018-05-25')%12 取出总年数后，剩余月数。

Q3: sqlserver计算时间的月份差

month(getdate())-month(createdate)-(case when day(getdate())day(createdate) then 1 else 0 end)