如何在两个日期之间获得年份 [英] How to get the year in between two dates

问题描述

Dear,

I have leave table

The Fields are

EmpNo
EmpName
LeaveStart
LeaveEnd


I want to show the leave taken by year.


i use datediff(dd,LeaveStart,LeaveEnd) it will shows the no. of days have taken.

but i want to show yearly wise.

E.g

2014 - 10 days
2015 - 16 days


how & where i can use year function becoz have leavestart & leaveend

suppose if employee taken a leave

LeavStart- 28-Dec-2015
LeaveEnd - 05-Jan-2016

then result should

2015 - 4 days
2016 - 5 days

then how to use query.

please help on this

Thanks
Basit.

我尝试了什么：



尚未尝试任何东西，只搜索到现在

What I have tried:

not yet tried anything, searching only till now

推荐答案

这不是最好看的查询，但这里有一些让你入门的东西。



在尝试将其放入查询表之前，请使用 IF [< a href =https://msdn.microsoft.com/en-GB/library/ms182717.aspx\"target =_ blanktitle =新窗口> ^ ]语句以确定您想要的内容发生。例如：

This is not going to be the best looking query but here are some things to get you started.

Before attempting to put it into a query for your table use an IF[^] statement to map out what you want to happen. For example:

declare @LeavStart date = '28-Dec-2015'
declare @LeaveEnd date =  '05-Jan-2016'

if [the years are the same]
	select datediff(dd, @LeavStart, @LeaveEnd)
else
begin
	declare @EndStartYear date -- this will be the last day of the first year
	declare @StartEndYear date -- this will be the first day of the 2nd year

	[Calculate the value of @EndStartYear]
	[Calculate the value of @StartEndYear]

	select datepart(yy, @LeavStart), datediff(dd, @LeavStart, @EndStartYear) + 1
	union
	select datepart(yy, @LeaveEnd), datediff(dd, @StartEndYear, @LeaveEnd) + 1
end

我已将方括号之间的位留给您，但您可能会发现这些链接很有用

DATEPART（Transact-SQL） [ ^ ]

一些常用日期例程 - SQLServerCentral [ ^ ]



由于有时需要使用UNION，因此将其放入针对您的表的单个查询中会更加困难。我最终使用公用表表达式 [ ^ ]

I've left the bits between the square brackets for you to do, but you may find these links useful
DATEPART (Transact-SQL)[^]
Some Common Date Routines - SQLServerCentral[^]

Getting that into a single query against your table is more difficult because of the UNION that is sometimes required. I ended up doing something like this with a Common Table Expression[^]

insert into emp values
('28-Dec-2015','05-Jan-2016'),
('28-Dec-2015','31-Dec-2015'),
('21-Dec-2014','31-Mar-2016')

;with results as -- this is defining my CTE
(
    select empId, LeaveYear = datepart(yy, LeavStart), 
	Y1 = CASE WHEN [the years are the same] THEN
	     [The difference between the dates]
	ELSE
	     [The difference between the start date and the end of that year]
	END
    FROM emp

    UNION

	select empId, datepart(yy, LeaveEnd), 
	     Y1 = CASE WHEN [the years are the same] THEN NULL -- Note!
	ELSE
	     [The difference between the start of the 2nd year and the end date]
	END 
	FROM emp
)
select * from results where Y1 IS NOT NULL
ORDER BY EmpId, LeaveYear

注意你一个NULL值，以便当日期在同一年时只返回一行。注意必须添加+1以使日期包含在内。

并查看我的示例数据会发生什么情况，其中empId = 3.您可能还需要对其进行排序。 />
这些是我得到的结果：

Note the use of the NULL value so that only one row is returned when the dates are in the same year. Watch out for having to add +1 to make the dates inclusive.
And have a look at what happens for my sample data where empId = 3. You may need to sort that out as well.
These are the results I got:

1	2015	4
1	2016	5
2	2015	3
3	2014	11
3	2016	91

如果您希望它们看起来像这样

If you want them to look like this instead

1	2015	4   2016	5
2	2015	3
3	2014	11  2016	91

然后你会发现这个链接很有用 - 在SQL查询中使用Pivot的简单方法 [ ^ ]

这篇关于如何在两个日期之间获得年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！