Click here to Skip to main content
15,569,644 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm try to enter a computed Column Specification (col7) in a column to get minimum date as opposed to a maximum date:

What I have tried:

this works fine:

however if I try to specify something like:

I keep getting sql 'error validating the formula for col7'
I'm just not sure how this should be written or even if it is possible in a Computed Column Specification.
for reference col5 is an integer and col6 is a date
How can I get this corrected in a Computed Column Specification?
Thanks in advance.
Updated 20-Jan-18 13:23pm

1 solution

I think the Computed Column Specification does not allow aggregation (Min, Max,...). You can try something like below. If 12/31/2099 > that the date dateadd([year],[col5],[col6]) then we can assume that the later is a min date, else the former is the min date

	[Year] [int] NULL,
	[Col5] [int] NULL,
	[Col6] [date] NULL,
	[Col7]  AS (case when dateadd(year,[col5],[col6])<'12/31/2099' then dateadd(year,[col5],[col6]) else '12/31/2099' end)

	SELECT 2018, 2, '2018-01-01' UNION
	SELECT 2018, 3, '2011-01-01' UNION
	SELECT 2014, 10, '2017-08-25' UNION
	SELECT 2017, 1, '2017-08-25' UNION
	SELECT 2020, 100, '2020-01-01' 


Year	Col5	Col6	        Col7
2014	10	    2017-08-25	    2027-08-25
2017	1	    2017-08-25	    2018-08-25
2018	2	    2018-01-01	    2020-01-01
2018	3	    2011-01-01	    2014-01-01
2020	100	    2020-01-01	    2099-12-31
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900