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:
<pre>dateadd(yyyy,[col5],[col6])

however if I try to specify something like:
Min((dateadd(year,[col5],[col6]),('12/31/2099')

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.
Posted
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

SQL
DECLARE @DateCom TABLE(
	[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)
)

INSERT INTO @DateCom
	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' 

SELECT * FROM @DateCom


OUTPUT:
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