13518219792
如：起始时间
成都创新互联公司主营灌南网站建设的网络公司,主营网站建设方案,APP应用开发,灌南h5微信小程序开发搭建,灌南网站营销推广欢迎灌南等地区企业咨询
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))
+
'日'
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())
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 取出总年数后，剩余月数。
month(getdate())-month(createdate)-(case when day(getdate())day(createdate) then 1 else 0 end)
