为创建日期增加90天

I'm grabbing the first day of the next quarter as StartDate. How can I add 90 days to it. The below is gives me invalid column error for StartDate within EndDate.

我把下个季度的第一天定为开始。我怎么能增加90天呢?下面给出了在EndDate中StartDate的无效列错误。

SELECT
    DATEADD(qq, DATEDIFF(qq, 0, MAX(Time_Stamp)) + 1, 0) as StartDate,
    DATEADD(day, 90, MAX(StartDate)) as EndDate
FROM Survey
WHERE MainHospital = 'Hospital1'

column

列

Time_Stamp
-----------
2014-06-04 16:01:14.000
2014-06-04 15:55:33.000
2014-06-04 15:45:05.000
2014-06-04 15:36:15.000
2014-06-04 15:00:34.000
2014-06-04 14:35:24.000
2014-06-04 14:04:50.000
2014-06-04 13:46:55.000
2014-06-04 13:23:57.000
2014-06-04 11:27:51.000

Just for refernce I found this which was very useful: http://zarez.net/?p=2484

为了便于参考，我发现这一点非常有用:http://zarez.net/?

So ended up with something like this:

结果是这样的:

SELECT 
     --Returns first day of next quarter
     DATEADD(qq, DATEDIFF(qq, 0, MAX(Time_Stamp)) + 1, 0) as StartDate,
     --Returns last day of next quarter
     DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, MAX(Time_Stamp)) +2, 0)) as EndDate
    FROM Survey
    WHERE MainHospital = 'Hospital1'

3 个解决方案

#1

The sql query doesn't know of column alias in the select. It is a self referencing error. The select statement creates the alias so it can't itself use the alias. The only statement to run after select is order by, where you can use an alias.

sql查询不知道select中的列别名。这是一个自引用错误。select语句创建别名，因此它自己不能使用别名。select之后要运行的唯一语句是order by，您可以在其中使用别名。

I would reuse your logic to prevent needing a subquery. Since ninety days is just another quater, change 1 to 2 and add another quarter to your Time Stamp.

我将重用您的逻辑以避免需要子查询。因为90天只不过是另一个四分之一，所以把1换成2，在你的时间戳上再加一个四分之一。

SELECT
    DATEADD(qq, DATEDIFF(qq, 0, MAX(Time_Stamp)) + 1, 0) as StartDate,
    DATEADD(qq, DATEDIFF(qq, 0, MAX(Time_Stamp)) + 2, 0) as EndDate
FROM Survey
WHERE MainHospital = 'Hospital1'

#2

It's invalid because StartDate is alias for your date calculation. You can use alias only when you have results. In other words alias is available when you have dataset returned from server.

它是无效的，因为StartDate是您的日期计算的别名。您只能在有结果时使用别名。换句话说，当从服务器返回数据集时，别名是可用的。

You can for example use subquery:

例如，可以使用subquery:

SELECT
  StartDate,
  DATEADD(day, 90, MAX(StartDate)) as EndDate
FROM (
  SELECT
     DATEADD(qq, DATEDIFF(qq, 0, MAX(Time_Stamp)) + 1, 0) as StartDate
  FROM Survey
  WHERE MainHospital = 'Hospital1'
) DS

#3

You can't reference a derived column by name within the same query. You will have to add the same logic here.

不能在同一查询中按名称引用派生列。你必须在这里添加相同的逻辑。

SELECT
DATEADD(qq, DATEDIFF(qq, 0, MAX(Time_Stamp)) + 1, 0) as StartDate,
DATEADD(day, 90, DATEADD(qq, DATEDIFF(qq, 0, MAX(Time_Stamp)) + 1, 0)) as EndDate
FROM Survey
WHERE MainHospital = 'Hospital1'

Not quite sure what you are trying to do with MAX in there so I just removed it.

我不太确定你想用MAX做什么所以我把它移走了。

#1