SQL min的日期计算列规范 [英] Ms SQL min date computed column specification

问题描述

我尝试在列中输入计算列规范（col7）以获得最小日期而不是最大日期：



什么我试过了：



一切正常：

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')

我一直得到sql'错误验证col7的公式'

我只是不确定应如何编写或者即使在计算列规范中也是如此。

作为参考col5是一个整数而col6是一个日期

如何在计算列规范中更正？

提前谢谢。

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.

推荐答案

我认为计算列规范不允许聚合（Min，Max，...）。您可以尝试类似下面的内容。如果12/31/2099>那个日期dateadd（[year]，[col5]，[col6] ]）然后我们可以假设后者是最小日期，否则前者是最小日期

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

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

这篇关于SQL min的日期计算列规范的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！