将varchar值'2 year -2 month 21 day'转换为数据类型int时转换失败

I am trying to find out for how long employees have been working for the company and I should also find out if any of the employees have a resignation date.

我想知道员工为公司工作了多长时间，我也应该知道是否有任何员工有辞职日期。

But I don't have data for this right now, therefore value of this column in my database is Null. But i consider in my sql when it's happened take Resignation Date instead current time But for now take work start date and current time. my theory is if the column resignation date is null, take "Current Date - Work start date", else should it take resignation date - Work start date.

但我现在没有这方面的数据，因此我的数据库中此列的值为Null。但是我在我的sql中考虑当它发生时采用辞职日期而不是当前时间但是现在需要工作开始日期和当前时间。我的理论是，如果列辞职日期为空，则采用“当前日期 - 工作开始日期”，否则应采取辞职日期 - 工作开始日期。

I wrote a SQL query for this theory and it's working fine but when I tried return for ex year month day for how long employees working I got a problem which is:

我为这个理论编写了一个SQL查询，它运行正常，但是当我尝试返回前一个月的工作时间，因为员工工作了多长时间我遇到了一个问题：

a ) I struggled with this error for hours and I also tried convert those lines, I thought its should be converted but it's not working well so, when I run my main query, I get this error:

a）我在这个错误中挣扎了几个小时，我也尝试转换这些行，我认为它应该被转换但是它运行不正常，所以当我运行我的主查询时，我收到此错误：

> Msg 245, Level 16, State 1, Line 1  
> Conversion failed when converting the varchar value '2 year -2 month 21 day' to data type int.

My main query looks like this:

我的主要查询如下所示：

**--- sql for how long employees work with year month day ----**
    CASE
        WHEN Users.ResignationDate IS NOT NULL 
           THEN DATEDIFF(YEAR, WorkStartDate, ResignationDate)
           ELSE CAST(DATEDIFF(yy, WorkStartDate, GETDATE()) AS varchar(4)) + ' year ' +
                CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()) AS varchar(2)) + ' month ' +
                CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()), DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate)), GETDATE()) AS varchar(2)) +' day'  END as Ancinitet,
        Paychecks.DepartmentName AS Afdelinger
   FROM dbo.Paychecks INNER JOIN  dbo.Users ON Users.Id=Paychecks.UserId
WHERE Users.CustomerId=214 order by Users.FirstName;

3 个解决方案

#1

A case expression returns a single value -- and a single type. Yours is returning two different types, and SQL Server decides that the appropriate return type is the first one, an integer.

case表达式返回单个值 - 和单个类型。你的返回两种不同的类型，SQL Server决定适当的返回类型是第一个，一个整数。

Just cast to a string:

只需转换为字符串：

(CASE WHEN Users.ResignationDate IS NOT NULL
      THEN CAST(DATEDIFF(YEAR, WorkStartDate, ResignationDate) as VARCHAR(255))
      ELSE (CAST(DATEDIFF(year, WorkStartDate, GETDATE()) AS varchar(4)) +' year ' +
            CAST(DATEDIFF(month, DATEADD(year, DATEDIFF(year, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()) AS varchar(2)) +' month ' +
            CAST(DATEDIFF(day, DATEADD(month, DATEDIFF(month, DATEADD(year, DATEDIFF(year, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()), DATEADD(year, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate)), GETDATE()) AS varchar(2)) +' day'
          )
END) as Ancinitet,

I suspect there is a better way to do what you want. If that is of interest, ask another question with sample data and desired results.

我怀疑有更好的方法可以做你想要的。如果感兴趣，请提出另一个问题，其中包含样本数据和所需结果。

#2

What's wrong with this?

这有什么问题？

datediff(year, workStartDate, isNull(resignationDate, getDate()))

#3

You'd be better off using weeks instead of months, since a week always contains 7 days. You could also use a 30 day month which will generally get you close but your days may be off by a couple.

你最好使用几周而不是几个月，因为一周总是包含7天。您也可以使用30天的月份，这通常可以让您关闭，但您的日子可能会被一对夫妇关闭。

DECLARE @StartDate DATETIME = '20151115',
        @EndDate DATETIME = GETDATE();

SELECT  YearWeekDay = CONCAT(DATEDIFF(DAY, @StartDate, @EndDate) / 365, ' year ', DATEDIFF(DAY, @StartDate, @EndDate) % 365 / 7, ' week ',
                              DATEDIFF(DAY, @StartDate, @EndDate) % 365 % 7, ' day'
                            ),
        YearMonthDay = CONCAT(DATEDIFF(DAY, @StartDate, @EndDate) / 365, ' year ', DATEDIFF(DAY, @StartDate, @EndDate) % 365 / 30, ' month ',
                               DATEDIFF(DAY, @StartDate, @EndDate) % 365 % 30, ' day '
                             ),
        YWDHMS = CONCAT( DATEDIFF(SECOND, @StartDate, @EndDate) / 86400 / 365, 'y ', DATEDIFF(SECOND, @StartDate, @EndDate) / 86400 % 365 / 7, 'w ',
                         DATEDIFF(SECOND, @StartDate, @EndDate) / 86400 % 365 % 7, 'd ', DATEDIFF(SECOND, @StartDate, @EndDate) % 86400 / 3600, 'h ',
                         DATEDIFF(SECOND, @StartDate, @EndDate) % 3600 / 60, 'm ', DATEDIFF(SECOND, @StartDate, @EndDate) % 60, 's '
                       );

Output:

输出：

StartDate   EndDate                 YearWeekDay             YearMonthDay            YWDHMS
2015-11-15  2017-02-22 15:11:34.123 1 year 14 week 2 day    1 year 3 month 10 day   1y 14w 2d 15h 11m 34s

#1