英文:
I want to create table or view from a recursion used to generate a date in ssms
问题
我编写了一个递归查询来生成日期列。我希望将这些日期存储为数据库中的表，但似乎找不到方法。
declare @startdate date = '2014-01-01';
declare @enddate date = '2023-12-31';
with calendar as
(
select @startdate as [orderDate]
union all
select DATEADD(dd,1,[orderdate])
from calendar
where DATEADD(dd,1,[orderdate])<= @enddate
)
select * from calendar
option (maxrecursion 0);
英文:
I wrote a recursive query to generate a column pf dates. I want the dates to be stored as a table in a db but can't seem to find a way.
declare @startdate date = '2014-01-01';
declare @enddate date = '2023-12-31';
with calendar as
(
select @startdate as [orderDate]
union all
select DATEADD(dd,1,[orderdate])
from calendar
where DATEADD(dd,1,[orderdate])<= @enddate
)
select * from calendar
option (maxrecursion 0);
答案1
得分: 0
你可以尝试使用以下代码来填充一个名为"your_table"的新表格中的日期。
你可以将其作为进一步操作的基础。
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
select
convert(date, dat ) Dat
into your_table
from
(
SELECT top 100 percent
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Line,
dateadd(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '2014-01-01') Dat
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1
) basis
where dat <= '2023-12-31'
英文:
you can try this one to fill a new table your_table with the dates.
You can use that as a basis for your further operations.
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
select
convert(date, dat ) Dat
into your_table
from
(
SELECT top 100 percent
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Line,
dateadd(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '2014-01-01') Dat
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1
) basis
where dat <= '2023-12-31'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论