SQL 查询以“yy 年 mm 月 dd 天”的格式查找员工的经验
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13579763/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to find out experience of employee in the format 'yy years mm months dd days'
提问by sanu j
I want a query to find out experience of employee in the format 'yy years mm months dd days'.
我想要一个查询，以“yy 年 mm 月 dd 天”的格式查找员工的经验。
SELECT EMPID, EMPNAME, DEPARTMENT, DESIGNATION, DATEDIFF(YEAR, DOJ, GETDATE()) AS EXPERIENCE,
EMPSTATUS AS JOB_STATUS
FROM EMPLOYEE
DOJ - field in db for saving 'date of joining' of employee. This is the query which returns experience in years only. How to modify it?
DOJ - 数据库中用于保存员工“加入日期”的字段。这是仅返回以年为单位的经验的查询。如何修改它？
采纳答案by valex
SELECT
EMPID, EMPNAME, DEPARTMENT, DESIGNATION,
convert(varchar(3),DATEDIFF(MONTH, DOJ, GETDATE())/12) +' years '+
convert(varchar(2),DATEDIFF(MONTH, DOJ, GETDATE()) % 12)+ ' months'
AS EXPERIENCE,
EMPSTATUS AS JOB_STATUS
FROM EMPLOYEE
回答by Kartiikeya
Consider Emp_joiningDate as column
考虑 Emp_joiningDate 作为列
SELECT DATEDIFF(year, Emp_joiningDate, GETDATE()) AS Years,
SELECT DATEDIFF(year, Emp_joiningDate, GETDATE()) AS Years,
DATEDIFF(month, Emp_joiningDate, GETDATE()) - DATEDIFF(year, '1/2/1999', GETDATE()) * 12 AS Months,
DATEDIFF(day, Emp_joiningDate, getdate())- DATEDIFF(month, '1/2/1999',
GETDATE()) - DATEDIFF(year, '1/2/1999', GETDATE()) * 365 as Days
回答by Olaf Dietsche
SQL Fiddlefor testing
用于测试的SQL Fiddle
SELECT EMPID, EMPNAME, DEPARTMENT, DESIGNATION,
cast(floor(experience / 365) as varchar) + ' years ' +
cast(floor(experience % 365 / 30) as varchar) + ' months ' +
cast(experience % 30 as varchar) + ' days' as experience,
EMPSTATUS AS JOB_STATUS
FROM (select *, datediff(DAY, doj, getdate()) as experience
from employee) t
回答by Saba
DECLARE @FromDate DATETIME = '2013-12-01 23:59:59.000',
@ToDate DATETIME = '2016-08-30 00:00:00.000',
DECLARE @MONTHS INT
SET @Months = DATEDIFF(MM,@FROMDATE,@TODATE)
IF (DATEPART(MM,@FromDate) <= (DATEPART(MM,@TODATE))+1)
BEGIN
SELECT CAST(DATEDIFF(YY,@FROMDATE,@TODATE) AS VARCHAR(5)) + ' Years '+ CAST( (DATEPART(MM,@TODATE)-DATEPART(MM,@FROMDATE)) AS VARCHAR(5))+1 +' Month'
END
ELSE
BEGIN
SELECT CAST(DATEDIFF(YY,@FROMDATE,@TODATE)-1 AS VARCHAR(5)) +' Years '+ CAST(12-(DATEPART(MM,@FROMDATE)) + DATEPART(MM,@TODATE)+1 AS VARCHAR(5) ) +' Month'
END
回答by Meera
create FUNCTION [dbo].[fn_getEmployePeriod] ( @dateofbirth DATETIME ) RETURNS VARCHAR(100) AS BEGIN DECLARE @currentdatetime DATETIME; DECLARE @years INT; DECLARE @months INT; DECLARE @days INT; DECLARE @currentMonthdays INT; DECLARE @result VARCHAR(100); SET @currentdatetime = GETDATE();--current datetime
create FUNCTION [dbo].[fn_getEmployePeriod] (@dateofbirth DATETIME) RETURNS VARCHAR(100) AS BEGIN DECLARE @currentdatetime DATETIME; 声明@years INT; 声明@months INT; 声明@days INT; 声明@currentMonthdays INT; 声明@result VARCHAR(100); SET @currentdatetime = GETDATE();--当前日期时间
IF ( @dateofbirth <= GETDATE() )
BEGIN
SELECT @years = DATEDIFF(YEAR, @dateofbirth, @currentdatetime); -- To find Years
SELECT @months = DATEDIFF(MONTH, @dateofbirth,
@currentdatetime) - ( DATEDIFF(YEAR,
@dateofbirth,
@currentdatetime)
* 12 );
SELECT @days = DATEPART(d, @currentdatetime) - DATEPART(d,
@dateofbirth);-- To Find Days
SELECT @currentMonthdays = ( SELECT DAY(DATEADD(DD, -1,
DATEADD(mm,
DATEDIFF(mm, 0,
GETDATE()), 0)))
);
IF ( @months < 0 )
BEGIN
SET @months = 12 + @months;
SET @years = @years - 1;
END;
IF ( @days < 0 )
BEGIN
SET @days = @currentMonthdays + @days;
IF(@months<>0)
begin
SET @months = @months - 1;
END
ELSE
BEGIN
SET @years = @years - 1;
SET @months = 11;
end
END;
-- To Find Months
-- 查找月份
SET @result = CAST(@years AS VARCHAR(3)) + ' years, '
+ CAST(@months AS VARCHAR(3)) + ' months, '
+ CAST(@days AS VARCHAR(3)) + ' days';
END;
ELSE
BEGIN
SET @result = 'Invaild date of birth';
END;
RETURN @result;
END;
回答by Mohanraj
SELECT convert(varchar(3),DATEDIFF(MONTH, '2015-01-01', GETDATE())/12) +' years '+
convert(varchar(2),DATEDIFF(DD, '2016-09-21', GETDATE()) % 12)+ ' months'
AS EXPERIENCE
