SQL案例变量

Question

我试图将这个CASE语句设置为一个变量，但我在列中得到了相同的日期。这条特殊的CASE语句正在根据夏令时进行调整。我在其他查询中都使用它，但我想缩短这些查询，只在顶部将它作为一个变量。

DECLARE @newdate as Datetime

SELECT @newdate = 
CASE WHEN 
(a.REQDATE BETWEEN '2014-11-02' AND '2015-03-08' 
OR a.REQDATE BETWEEN '2015-11-01' AND '2016-03-13'
OR a.REQDATE BETWEEN '2016-11-06' AND '2017-03-12'
OR a.REQDATE BETWEEN '2017-11-05' AND '2018-03-11'
OR a.REQDATE BETWEEN '2018-11-04' AND '2019-03-10')
THEN     CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,a.REQDATE),'-08:00'))
ELSE     CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,a.REQDATE),'-07:00'))
END

FROM TABLE a inner join TABLE b on a.REQDATE = b.REQDATE


SELECT
@newdate, b.REQDATE --control column
FROM TABLE b

结果：

(No column name)        | REQDATE
2016-04-08 13:00:52.000 | 2015-11-02 15:41:25.000   
2016-04-08 13:00:52.000 | 2016-02-02 10:25:54.000   
2016-04-08 13:00:52.000 | 2016-02-11 12:59:10.000   

让我在这里添加更多信息。我想替换以下代码：

SELECT
CASE WHEN (b.REQDATE BETWEEN '2014-11-02' AND '2015-03-08' 
OR b.REQDATE BETWEEN '2015-11-01' AND '2016-03-13'
OR b.REQDATE BETWEEN '2016-11-06' AND '2017-03-12'
OR b.REQDATE BETWEEN '2017-11-05' AND '2018-03-11'
OR b.REQDATE BETWEEN '2018-11-04' AND '2019-03-10')
THEN     CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,b.REQDATE),'-08:00'))
ELSE CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,b.REQDATE),'-07:00')) END         AS "DateOpened"
FROM TABLE

有了这个：

SELECT
@newdate
FROM TABLE

基本上就是在CASE语句之外创建一个变量

Answer 1

一个变量只能包含一个值。您需要做的是将case语句移动到一个函数中...

CREATE FUNCTION dbo.DST(@d datetime) 
RETURNS datetime 
AS
BEGIN
    RETURN CASE WHEN 
    (@d BETWEEN '2014-11-02' AND '2015-03-08' 
    OR @d BETWEEN '2015-11-01' AND '2016-03-13'
    OR @d BETWEEN '2016-11-06' AND '2017-03-12'
    OR @d BETWEEN '2017-11-05' AND '2018-03-11'
    OR @d BETWEEN '2018-11-04' AND '2019-03-10')
    THEN     CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,@d),'-08:00'))
    ELSE     CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,@d),'-07:00'))
    END
END
GO

SqlFiddle

Answer 2

声明一个临时表并向其中插入记录，而不是@newDate变量。完成后，只需将新表数据与表b或需要关联并获得结果的任何其他表进行内部连接即可。