I was hoping I could get some help with this Computed column. I've given up trying to get it to work. It works in most cases but not all. It's very random when it works and when it does not.
There are 2 fields involved:
YR - a 4 character string containing a year, e.g. "2015"
PDate - a Date, e.g. 2014-10-03
I want to create a computed field where the Month and Day come from PDate, and the Year is YR when the Month is 1 through 9 and it's YR -1 when the month is 10-12.
That's it. Here's what I have, which seems to work if I do it in a Select statement but does not always work if I do it as a Computed column. When I say it fails randomly, I mean that with the same values in YR and PDate, sometimes it works and sometimes it comes up with some completely random date.
(case when datepart(month,[PDate])<(10)
then CONVERT(varchar,[YR],0)
else CONVERT(varchar,[YR]-(1),0)
end
The following table lists the styles for which the string-to-datetime conversion is nondeterministic.
All styles below 100 *1
106
107
109
113
130
*1 With the exception of styles 20 and 21
You're using 0, which is nondeterministic according to the above.
DATEADD(YEAR, -YEAR(pdate) + [YR] - case when MONTH(pdate) >= 10 then 1 else 0 end, pdate)
For example:
select *, DATEADD(YEAR, -YEAR(pdate) + [YR] - case when MONTH(pdate) >= 10 then 1 else 0 end, pdate)
from (
select '2015' as yr, CAST('20141003' as date) as pdate union all
select '2015', '20140930'
) as test_data