将多个DATE值插入列中。 [英] Inserting Multiple DATE values into a column.
问题描述
嗨大家好，有一段时间以来我已经发出请求帮助，我想我已经忘记了从这个
新闻组的有用贡献者那里学到的一切，但是说：
我试图在过去两年后插入一系列日期进入
第一列说两列表。我一直在尝试使用''当前
date''并从那里向后工作但是对于我的生活我无法获得
语法正确。我可以生成单个值，但无法获得一个列表，
将是过去两年中日期格式的所有WEEKDAYS。使用VALUES
而不是SELECT语句，如何获得列表而不是单个
值。 VALUES并不喜欢BETWEEN而且我不知道如何使用
递归语句。
SELECT意味着我已经在表中有值了VALUES作为一个
函数应该能以某种方式为我计算这些。任何帮助都会非常感激。
TD
WITH
- 生成日期
TYEARS（DT，CNT，WD）AS
（
- SEED
价值（日期（''2002-01-01''），1，DAYOFWEEK_ISO（日期（''2002-01-01''）））
UNION ALL
SELECT TYEARS.DT + 1天，CNT + 1，DAYOFWEEK_ISO（DATE（TYEARS.DT）+ 1天）
来自TYESES
- 限制数据生成到某个时间限制
在哪里TYEARS.CNT<（366 * 2）
）
- 没有日期周末
- （a）
选择
RCALC1.DT
来自
（
SELECT DT FROM TYCESS
WHERE WD NOT IN（6,7）
）AS RCALC1（DT）
- 在哪里DT之间...或年份（）......或者......
订购DT
;
如果需要，可以使用插入而不是选择（a）。 （插入x
select * from ...）
我没有打扰性能...
声明可能更紧凑但我也没打扰。
PM
Tim，
试试这个：
CREATE TABLE DATE_SEQ（SOME_DATE DATE NOT NULL）;
INSERT INTO DATE_SEQ SELECT DATE（''01/01/2003''）+（ ROW_NUMBER（）OVER（）
- 1）来自SYSCAT.COLUMNS的日期;
" Tim Davidge" < tdavidge@no_spam.hotmail.com>在消息新闻中写道：< jq ******************** @ comcast.com> ...
我'我试图在过去的两年后插入一系列日期，比如两列表格。
blockquote>
递归查询将是最明显的解决方案。你需要将
插入适当的INSERT（例如
，然后加入你的桌面以避免重复插入）：
WITH
日期（日期）AS
（
价值当前日期 - 2年
UNION ALL
选择
日期+ 1天
来自
日期
WHERE
date< = CURDENT DATE
）
SELECT date FROM dates WHERE dayofweek_iso（date）< 6
Jeremy Rickard
Hi folks, been a while since I have posted a plea for help and I think I
have forgotten everything I learnt from the helpful contributors to this
newsgroup, that said however :
I''m trying to insert a range of dates for the past say two years into the
first column of say a two column table. I''ve been trying to use ''current
date'' and working backwards from there but for the life of me I cannot get
the syntax right. I can produce a single value, but cannot get a list that
would be all WEEKDAYS in date format for the last two years. Using VALUES
instead of a SELECT statement, how do I get a list instead of a single
value. VALUES doesnt sem to like BETWEEN and I do not know how to use
recursive statements.
SELECT would imply I already have the values in a table, where VALUES as a
function should somehow be able to calculate these for me. Any help would be
greatly appreciated.
TD解决方案WITH
-- GENERATE DATES
TYEARS(DT, CNT, WD) AS
(
-- SEED
VALUES( DATE(''2002-01-01''), 1, DAYOFWEEK_ISO(DATE(''2002-01-01'')) )
UNION ALL
SELECT TYEARS.DT + 1 DAY, CNT + 1, DAYOFWEEK_ISO(DATE(TYEARS.DT) + 1 DAY)
FROM TYEARS
-- LIMIT DATA GENERATION TO A CERTAIN TIME LIMIT
WHERE TYEARS.CNT < (366*2)
)
-- DATES WITHOUT THE WEEKENDS
-- (a)
SELECT
RCALC1.DT
FROM
(
SELECT DT FROM TYEARS
WHERE WD NOT IN (6, 7)
) AS RCALC1(DT)
-- WHERE DT BETWEEN ... OR YEAR() ... OR ...
ORDER BY DT
;
You can use an insert instead of the select (a) if you want. (insert into x
select * from ...)
I did not bother about performance...
The statement could be more compact but i did not bother either.
PM
Tim,
try this:
CREATE TABLE DATE_SEQ(SOME_DATE DATE NOT NULL);
INSERT INTO DATE_SEQ SELECT DATE(''01/01/2003'') + (ROW_NUMBER() OVER()
- 1) DAYS FROM SYSCAT.COLUMNS;
"Tim Davidge" <tdavidge@no_spam.hotmail.com> wrote in message news:<jq********************@comcast.com>...
I''m trying to insert a range of dates for the past say two years into the
first column of say a two column table.
Recursive query would be the most obvious solution. You''ll need to
plug something like the following into an appropriate INSERT (e.g.
maybe joining back to your table to avoid duplicate inserts):
WITH
dates(date) AS
(
VALUES CURRENT DATE - 2 YEARS
UNION ALL
SELECT
date + 1 DAY
FROM
dates
WHERE
date <= CURRENT DATE
)
SELECT date FROM dates WHERE dayofweek_iso(date) < 6
Jeremy Rickard
这篇关于将多个DATE值插入列中。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
