I have two dates start date and end date . How can I know if between these two dates , I got 01/01 meaning new year .
我有两个日期开始日期和结束日期。我怎么知道这两个日期之间是否有01/01意味着新的一年。
For example :
例如 :
Table test
id start date End date
1 02/03/2013 19/09/2016
2 15/02/2015 30/06/2015
Output
id start date End date
1 02/03/2013 31/12/2013
1 01/01/2014 31/12/2014
1 01/01/2015 31/12/2015
1 01/01/2016 19/09/2016
2 15/02/2015 30/06/2015
How to do something like that ?
怎么做那样的事情?
2 个解决方案
#1
1
You would know if the year component is different:
您会知道年份组件是否不同:
select t.*,
(case when year(startdate) < year(enddate) then 1
else 0
end) as HasNewYear
from table t;
#2
0
You can use a recursive CTE to find all years between the two dates, then cast out the dates to DATETIME
objects to get the desired output:
您可以使用递归CTE查找两个日期之间的所有年份,然后将日期转换为DATETIME对象以获得所需的输出:
;WITH CTE AS (
SELECT ID, DATEPART(YEAR, STARTDATE) AS YR, ENDDATE
FROM TABLE1
UNION ALL
SELECT ID, YR+1, ENDDATE
FROM CTE
WHERE YR < DATEPART(YEAR,ENDDATE))
SELECT A.ID, CASE WHEN CAST(A.YR AS VARCHAR)+'-01-01' > B.STARTDATE THEN CAST(A.YR AS VARCHAR)+'-01-01' ELSE B.STARTDATE END AS STARTDATE, CASE WHEN CAST(A.YR AS VARCHAR)+'-12-31' < B.ENDDATE THEN CAST(A.YR AS VARCHAR)+'-12-31' ELSE B.ENDDATE END AS ENDDATE
FROM CTE AS A
JOIN TABLE1 AS B
ON A.ID=B.ID
ORDER BY A.ID
#1
1
You would know if the year component is different:
您会知道年份组件是否不同:
select t.*,
(case when year(startdate) < year(enddate) then 1
else 0
end) as HasNewYear
from table t;
#2
0
You can use a recursive CTE to find all years between the two dates, then cast out the dates to DATETIME
objects to get the desired output:
您可以使用递归CTE查找两个日期之间的所有年份,然后将日期转换为DATETIME对象以获得所需的输出:
;WITH CTE AS (
SELECT ID, DATEPART(YEAR, STARTDATE) AS YR, ENDDATE
FROM TABLE1
UNION ALL
SELECT ID, YR+1, ENDDATE
FROM CTE
WHERE YR < DATEPART(YEAR,ENDDATE))
SELECT A.ID, CASE WHEN CAST(A.YR AS VARCHAR)+'-01-01' > B.STARTDATE THEN CAST(A.YR AS VARCHAR)+'-01-01' ELSE B.STARTDATE END AS STARTDATE, CASE WHEN CAST(A.YR AS VARCHAR)+'-12-31' < B.ENDDATE THEN CAST(A.YR AS VARCHAR)+'-12-31' ELSE B.ENDDATE END AS ENDDATE
FROM CTE AS A
JOIN TABLE1 AS B
ON A.ID=B.ID
ORDER BY A.ID