我想从在SSMS中用于生成日期的递归中创建表格或视图。

huangapple go评论86阅读模式
英文:

I want to create table or view from a recursion used to generate a date in ssms

问题

我编写了一个递归查询来生成日期列。我希望将这些日期存储为数据库中的表,但似乎找不到方法。

  1. declare @startdate date = '2014-01-01';
  2. declare @enddate date = '2023-12-31';
  3. with calendar as
  4. (
  5. select @startdate as [orderDate]
  6. union all
  7. select DATEADD(dd,1,[orderdate])
  8. from calendar
  9. where DATEADD(dd,1,[orderdate])<= @enddate
  10. )
  11. select * from calendar
  12. 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.

  1. declare @startdate date = &#39;2014-01-01&#39;;
  2. declare @enddate date = &#39;2023-12-31&#39;;
  3. with calendar as
  4. (
  5. select @startdate as [orderDate]
  6. union all
  7. select DATEADD(dd,1,[orderdate])
  8. from calendar
  9. where DATEADD(dd,1,[orderdate])&lt;= @enddate
  10. )
  11. select * from calendar
  12. option (maxrecursion 0);

答案1

得分: 0

你可以尝试使用以下代码来填充一个名为"your_table"的新表格中的日期。

你可以将其作为进一步操作的基础。

  1. WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
  2. select
  3. convert(date, dat ) Dat
  4. into your_table
  5. from
  6. (
  7. SELECT top 100 percent
  8. ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Line,
  9. dateadd(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '2014-01-01') Dat
  10. FROM x ones, x tens, x hundreds, x thousands
  11. ORDER BY 1
  12. ) basis
  13. 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.

  1. WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
  2. select
  3. convert(date, dat ) Dat
  4. into your_table
  5. from
  6. (
  7. SELECT top 100 percent
  8. ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Line,
  9. dateadd(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), &#39;2014-01-01&#39;) Dat
  10. FROM x ones, x tens, x hundreds, x thousands
  11. ORDER BY 1
  12. ) basis
  13. where dat &lt;= &#39;2023-12-31&#39;

huangapple
  • 本文由 发表于 2023年2月7日 02:34:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/75365284.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定