当我运行以下查询0年时，要避免

问题描述：

select 
  Convert(varchar(100), datediff(year, 0, ET-ST)) + 'years' +
  Convert(varchar(100), datepart(month, ET-ST) - 1) + 'months' +
  Convert(varchar(100), datepart(day, ET-ST) - 1) + 'days'
from
  (
    select  
      ST = convert(datetime, '2016-05-26 20:03:28.127'),
      ET = convert(datetime, getdate())
  ) a

当我运行上述查询输出时如下

0years2months1days。

但我想避免0年

i只想显示以下输出

2个月1天。只有

我想在上面的查询中做些什么改变。

我是什么尝试过：

我的输出中避免0年

当我运行以下查询0年要避免

when i run the above query output as follows

0years2months1days.

but i want to avoid 0 years

i want to show only the below output

2months1days. only

for that what changes i want to do in above query.

What I have tried:

avoid 0 years in my output

when i run the below query 0 years to be avoided

答

您应该添加 CASE-WHEN-THEN-ELSE-END [ ^ ]在第一行...

这样的事情：

You should add a CASE-WHEN-THEN-ELSE-END[^] at the first line...
Something like this:

CASE WHEN DATEDIFF(YEAR, 0, ET-ST) <> 0 THEN CONVERT(VARCHAR(100), DATEDIFF(YEAR, 0, ET-ST)) + ' YEARS, ' ELSE '' END +

你好，

简单添加一个额外的 Case 条件以避免0年。

Hello ,
Simple add one extra Case condition to avoid 0 Year .

select case when datediff(year,0,ET-ST)>0 then Convert(varchar(100),datediff(year,0,ET-ST)) + 'years' else '' end     + 
Convert(varchar(100),datepart(month,ET-ST)-1 )+ 'months'+Convert(varchar(100),
datepart(day,ET-ST)-1) +'days'
from
(
select 
ST = convert(datetime,'2015-03-26 20:03:28.127'),
ET = convert(datetime,getdate())
) a

谢谢

Thanks

当我运行以下查询0年时，要避免

相关推荐