Split a date into two dates

Question

I want to splite two dates into four dates:

Date1:04/01/2012
Date2:12/05/2015

The result that I want is

If datepart(year,date2)=datepart(year,getdate())
Case1
Date1:04/01/2012
Date2:31/12/2014
Date3:01/01/2015
Date4:12/05/2015
Else
Case2
Date1:04/01/2012
Date2:12/05/2015

My question how to obtain date2 and date3 in case1?

score 0 · Answer 1 · answered Jun 05 '15 at 23:15

0

You can kind of create them like this:

select '01/01/'+(select cast(datepart(yy,getdate()) as varchar))
select '31/12/'+(select cast(datepart(yy,getdate())-1 as varchar))

answered Jun 05 '15 at 23:15

russ

579
3
7

Thank you but i want a solution whatever the date format! – Oca ocp Jun 05 '15 at 23:24
[Date values have no format](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) – Zohar Peled Jun 07 '15 at 08:22

Zohar Peled · Answer 2 · 2015-06-07T08:31:36.697

If I understand you correctly you want to add "fake" records to your select statement if the first date is in a year that is before the current year and the second one is in the current year.
I've taken the liberty to assume you don't want to add 31/12/ if the first date is actually the 31 of December in the last year.

Here is my suggestion:

;With cte as (
SELECT DateValue, ROW_NUMBER() OVER (ORDER BY DateValue) As rn
FROM Tbl
)

-- Get the first date
SELECT DateValue
FROM cte 
WHERE rn = 1

UNION ALL

/*
  Add the last date of the previous year. 
  The where clause will enable you to add this to the select result only on your terms
  (if the second date is on the current year and the first date is before Dec 31th of the last year)
*/
SELECT CAST(CAST(YEAR(GETDATE())-1 as varchar) + '-12-31' as date)
FROM cte 
WHERE rn = 2
AND YEAR(DateValue) = YEAR(GETDATE())
AND CAST(CAST(YEAR(GETDATE())-1 as varchar) + '-12-31' as date) > (SELECT DateValue FROM cte WHERE rn = 1)

UNION ALL

/*
  Add the first date of the current year. 
  Note that the where clause here is only testing that the second date is on the current year, 
  while the first date is before the current year. 
  So it will add the Jan 1st of the current year even if the first date is Dec 31th of the last year.
*/
SELECT CAST(CAST(YEAR(GETDATE()) as varchar) + '-01-01' as date)
FROM cte 
WHERE rn = 2 
AND YEAR(DateValue) = YEAR(GETDATE())
AND YEAR(GETDATE()) > (SELECT YEAR(DateValue) FROM cte WHERE rn = 1)

UNION ALL

-- add the second date
SELECT DateValue
FROM cte WHERE rn = 2

You can see it working on this fiddle.

Split a date into two dates

2 Answers2