replace(DATEADD(CURRENT_DATE,-1,'dd'),'-','')

您可以使用以下代码来替换DATEADD函数的一部分：

SELECT REPLACE(DATEADD(day, -1, CURRENT_DATE), '-', '') AS Result;

这将返回当前日期减去一天后的日期，并将结果中的连字符替换为空字符串。请注意，具体的语法可能因不同的数据库系统而有所不同。以上示例是基于SQL语言的通用语法。

相关问题

解释一下这段代码 with a as ( SELECT CONVERT(nvarchar(10), getdate() ,120) AS datatime,1 as sortnum union all SELECT CONVERT(nvarchar(10), DATEADD(day, -1, getdate()) ,120) AS datatime,2 as sortnum union all SELECT CONVERT(nvarchar(10), DATEADD(day, -2, getdate()) ,120) AS datatime,3 as sortnum union all SELECT CONVERT(nvarchar(10), DATEADD(day, -3, getdate()) ,120) AS datatime,4 as sortnum union all SELECT CONVERT(nvarchar(10), DATEADD(day, -4, getdate()) ,120) AS datatime,5 as sortnum union all SELECT CONVERT(nvarchar(10), DATEADD(day, -5, getdate()) ,120) AS datatime,6 as sortnum union all SELECT CONVERT(nvarchar(10), DATEADD(day, -6, getdate()) ,120) AS datatime,7 as sortnum ), b as ( select GET_DATE,TAG_VALUE from T_TAG_DATA where TAG_ID = 1408 ) , -- 当日产量 c as ( select a.datatime,ISNULL(sum(b.TAG_VALUE),0) as 当日产量 from a left join b on a.datatime = SUBSTRING(CONVERT(varchar(100), b.GET_DATE, 120),1,10) GROUP BY a.datatime ), -- 台时产量 d as( select a.datatime,b.TAG_VALUE as 台时产量 from a left join b on a.datatime = SUBSTRING(CONVERT(varchar(100), b.GET_DATE, 120),1,10) where SUBSTRING(CONVERT(varchar,GETDATE(),120),12,2) = SUBSTRING(CONVERT(varchar(100), b.GET_DATE, 120),12,2) ), -- 累计产量 e as ( SELECT CONVERT(nvarchar(10), getdate() ,120) AS datatime,sum(TAG_VALUE) as 累计产量 from b where GET_DATE <= getdate() union all SELECT CONVERT(nvarchar(10), DATEADD(day, -1, getdate()) ,120) AS datatime,sum(TAG_VALUE) as 累计产量 from b where GET_DATE <= DATEADD(day, -1, getdate()) union all SELECT CONVERT(nvarchar(10), DATEADD(day, -2, getdate()) ,120) AS datatime,sum(TAG_VALUE) as 累计产量 from b where GET_DATE <= DATEADD(day, -2, getdate()) union all SELECT CONVERT(nvarchar(10), DATEADD(day, -3, getdate()) ,120) AS datatime,sum(TAG_VALUE) as 累计产量 from b where GET_DATE <= DATEADD(day, -3, getdate()) union all SELECT CONVERT(nvarchar(10), DATEADD(day, -4, getdate()) ,120) AS datatime,sum(TAG_VALUE) as 累计产量 from b where GET_DATE <= DATEADD(day, -4, getdate()) union all SELECT CONVERT(nvarchar(10), DATEADD(day, -5, getdate()) ,120) AS datatime,sum(TAG_VALUE) as 累计产量 from b where GET_DATE <= DATEADD(day, -5, getdate()) union all SELECT CONVERT(nvarchar(10), DATEADD(day, -6, getdate()) ,120) AS datatime,sum(TAG_VALUE) as 累计产量 from b where GET_DATE <= DATEADD(day, -6, getdate()) ) select SUBSTRING(a.datatime,6,10) as datatime,ISNULL(c.当日产量,0) as 当日产量,ISNULL(d.台时产量,0) as 台时产量,ISNULL(e.累计产量,0) as 累计产量 from a left join c on a.datatime = c.datatime left join d on a.datatime = d.datatime left join e on a.datatime = e.datatime order by a.datatime asc

这段代码是一个 SQL 查询语句，主要是用于查询某个时间范围内的产量数据。其中，第一部分使用了 with 关键字，创建了一个名为 a 的子查询，生成了过去 7 天的日期和序号；第二部分使用了名为 b 的子查询，查询了一个特定的 TAG_ID 的产量数据；第三部分使用了名为 c 的子查询，查询了每天的产量数据，并计算出每天的当日产量；第四部分使用了名为 d 的子查询，查询了每小时的产量数据，并计算出当天的台时产量；第五部分使用了名为 e 的子查询，查询了过去 7 天的累计产量数据；最后将 a, c, d, e 四个子查询进行 left join 操作，生成了最终的查询结果。

修改完整版的sql存储（积分兑换脚本），改写出可执行的hive脚本create procedure "rptdev"."sp_wjq_jf_value_dhfz_show"( in @data_date integer default cast("dateformat"("now"()-1,'yyyymmdd') as integer), in @is_run_flag1 integer default 1, in @is_run_flag2 integer default 10 ) on exception resume /****************************************************************** -- Purpose : 兑换积分分析报表 -- Auther: xxx -- Date : 20210923 *******************************************************************/ begin declare @stat_date varchar(8); --统计日期 declare @month_id integer; --统计月份 declare @month_first_date varchar(8); --统计月的第一天 declare @month_last_date varchar(8); --统计月的最后一天 declare @last_month_first_date varchar(8); --上月的第一天 declare @last_month_last_date varchar(8); --上月的最后一天 declare @last_month integer; --上月 declare @last_last_month integer; --上上月 declare @last_month_this_day varchar(8); --上月当天 declare @last_year_this_month integer; --上年当月 declare @this_year_first_month integer; --本年首月 declare @last_year_last_month integer; --上年尾月 set @stat_date = convert(varchar(8),@data_date); set @month_id = @data_date/100; set @month_first_date = "dateformat"(@data_date,'yyyymm01'); --统计月的第一天 set @month_last_date = "dateformat"("dateadd"("dd",-1,"dateformat"("dateadd"("mm",1,@data_date),'yyyymm01')),'yyyymmdd'); --统计月的最后一天 set @last_month_first_date = "dateformat"("dateadd"("mm",-1,@data_date),'yyyymm01'); --上月第一天 set @last_month_last_date = "dateformat"("dateadd"("dd",-1,"dateformat"("dateadd"("mm",1,@data_date),'yyyymm01')),'yyyymmdd'); set @last_month = cast("dateformat"("dateadd"("month",-1,@data_date),'yyyymm') as integer); --上月 set @last_last_month = cast("dateformat"("dateadd"("month",-2,@data_date),'yyyymm') as integer); --上上月 set @last_month_this_day = "dateformat"("dateadd"("mm",-1,convert(date,@data_date)),'yyyymmdd'); --上月当天 set @last_year_this_month = convert(integer,"dateformat"("dateadd"("yy",-1,@last_month_this_day),'yyyymm')); --上年当月

--将存储过程改写成Hive脚本
--设置变量
SET data_date = from_unixtime(unix_timestamp()-86400,'yyyyMMdd');
SET is_run_flag1 = 1;
SET is_run_flag2 = 10;

--获取日期相关变量
SET stat_date = CAST(data_date AS VARCHAR(8));
SET month_id = CAST(data_date/100 AS INT);
SET month_first_date = CONCAT(SUBSTR(data_date, 1, 6), '01');
SET month_last_date = DATE_FORMAT(DATE_ADD(month_first_date, INTERVAL 1 MONTH), 'yyyyMMdd') - 1;
SET last_month_first_date = DATE_FORMAT(DATE_ADD(month_first_date, INTERVAL -1 MONTH), 'yyyyMMdd');
SET last_month_last_date = DATE_FORMAT(DATE_ADD(month_first_date, INTERVAL -1 DAY), 'yyyyMMdd');
SET last_month = CAST(DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 MONTH), 'yyyyMM') AS INT);
SET last_last_month = CAST(DATE_FORMAT(DATE_ADD(data_date, INTERVAL -2 MONTH), 'yyyyMM') AS INT);
SET last_month_this_day = DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 MONTH), 'yyyyMMdd');
SET last_year_this_month = CAST(DATE_FORMAT(DATE_ADD(last_month_this_day, INTERVAL -1 YEAR), 'yyyyMM') AS INT);
SET this_year_first_month = CAST(DATE_FORMAT(DATE_ADD(data_date, INTERVAL -MONTH(data_date) MONTH), 'yyyyMM') AS INT);
SET last_year_last_month = CAST(DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 YEAR), 'yyyyMM') AS INT);

--创建临时表
DROP TABLE IF EXISTS tmp_jf_value_dhfz_show;
CREATE TABLE tmp_jf_value_dhfz_show AS
SELECT
t1.cust_id,
t1.cust_name,
t2.channel_name,
t1.consume_value,
t1.exchange_value,
t1.this_month_value,
t1.last_month_value,
t1.last_last_month_value,
t1.last_year_this_month_value,
t1.this_year_first_month_value,
t1.last_year_last_month_value,
t1.month_id
FROM
(
--获取本月和上月的兑换积分数
SELECT
cust_id,
cust_name,
SUM(CASE WHEN consume_date >= month_first_date AND consume_date <= month_last_date THEN consume_value ELSE 0 END) AS consume_value,
SUM(CASE WHEN exchange_date >= month_first_date AND exchange_date <= month_last_date THEN exchange_value ELSE 0 END) AS exchange_value,
SUM(CASE WHEN consume_date >= last_month_first_date AND consume_date <= last_month_last_date THEN consume_value ELSE 0 END) AS last_month_value,
SUM(CASE WHEN exchange_date >= last_month_first_date AND exchange_date <= last_month_last_date THEN exchange_value ELSE 0 END) AS last_month_exchange_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(last_month_first_date, INTERVAL -1 MONTH), 'yyyyMMdd')
AND consume_date <= DATE_FORMAT(DATE_ADD(last_month_last_date, INTERVAL -1 MONTH), 'yyyyMMdd') THEN consume_value ELSE 0 END) AS last_last_month_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(last_month_this_day, INTERVAL -1 YEAR), 'yyyyMMdd')
AND consume_date <= DATE_FORMAT(DATE_ADD(last_month_this_day, INTERVAL -1 YEAR MONTH), 'yyyyMM') THEN consume_value ELSE 0 END) AS last_year_this_month_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -MONTH(data_date) MONTH), 'yyyyMMdd')
AND consume_date <= month_last_date THEN consume_value ELSE 0 END) AS this_month_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 YEAR), 'yyyyMM')
AND consume_date <= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 MONTH), 'yyyyMM') THEN consume_value ELSE 0 END) AS last_year_last_month_value,
SUM(CASE WHEN consume_date >= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -MONTH(data_date) MONTH), 'yyyyMM')
AND consume_date <= DATE_FORMAT(DATE_ADD(data_date, INTERVAL -1 MONTH), 'yyyyMM') THEN consume_value ELSE 0 END) AS this_year_first_month_value,
month_id
FROM
jf_value_detail
GROUP BY
cust_id,
cust_name,
month_id
) t1
LEFT JOIN
(
--获取渠道名称
SELECT
channel_id,
channel_name
FROM
channel_info
) t2
ON t1.channel_id = t2.channel_id;

--根据条件筛选记录
DROP TABLE IF EXISTS jf_value_dhfz_show;
CREATE TABLE jf_value_dhfz_show AS
SELECT
cust_id,
cust_name,
channel_name,
consume_value,
exchange_value,
this_month_value,
last_month_value,
last_last_month_value,
last_year_this_month_value,
this_year_first_month_value,
last_year_last_month_value,
month_id
FROM
tmp_jf_value_dhfz_show
WHERE
((is_run_flag1 = 1 AND consume_value > 0) OR (is_run_flag1 = 0))
AND ((is_run_flag2 = 10 AND channel_id = 10) OR (is_run_flag2 <> 10 AND channel_id <> 10));

--删除临时表
DROP TABLE IF EXISTS tmp_jf_value_dhfz_show;