sql server - Casting gives me wrong date? -

select datepart(year, purchase_date) transaction_year, datepart(month, purchase_date) transaction_month, case when datepart(year, due_date) >= datepart(year, purchase_date) datepart(year, due_date) else datepart(year, purchase_date) end release_year, case when (datepart(year, due_date) = datepart(year, purchase_date) , datepart(month, due_date) >= datepart(month, purchase_date)) datepart(month, due_date) when datepart(year, due_date) > datepart(year, purchase_date) datepart(month, due_date) else datepart(month, purchase_date) end release_month, cast(year(case when datepart(year, due_date) >= datepart(year, purchase_date) datepart(year, due_date) else datepart(year, purchase_date) end) varchar(4)) + right('0'+cast(month(case when (datepart(year, due_date) = datepart(year, purchase_date) , datepart(month, due_date) >= datepart(month, purchase_date)) datepart(month, due_date) when datepart(year, due_date) > datepart(year, purchase_date) datepart(month, due_date) else datepart(month, purchase_date) end) varchar(2)),2) test 

on sample table of 6 records generates

2012 1 2012 1 190501 2012 1 2013 12 190501 2011 1 2012 1 190501 2011 1 2011 1 190501 2012 1 2012 1 190501 2012 1 2012 2 190501 

what doing wrong?

many thanks!

why trying in 1 go , repeating on place.

if start

select datepart(year,purchase_date) purchase_year, datepart(year,due_date) due_year, datepart(month,purchase_date) purchase_month, datepart(month,due_date) due_month test 

then can do

select purchase_year transaction_year, purchase_month) transaction_month, case when due_year >= purchase_year due_year else purchase_year end release_year, case when (due_year = purchase_year) , (due_month >= purchase_month) due_month when due_year > purchase_year due_month else purchase_month end release_month, cast( cast(release_year varchar(4)) + '-' + cast(release_month varchar(2)) + '-1' date) release_date ( select datepart(year,purchase_date) purchase_year, datepart(year,due_date) due_year, datepart(month,purchase_date) purchase_month, datepart(month,due_date) due_month test ) somedummytablename 

i think after, , may have got right after wading through morass.