How to generate list of dates — ie every day for the past 2 years? (sql server)












2















We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value.



I'm wondering what's the simplest way to virtually generate a table that simply lists dates in one column, literally every date from '2014-01-01' to the current date (the other columns I can use a formula from there). Actually even appending future dates on for a year would probably be useful as well.



Now yes I can grab distinct dates from another random fact table that has thousands of entries, but that seems sloppy and is creating a dependency where there really shouldn't be one.










share|improve this question





























    2















    We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value.



    I'm wondering what's the simplest way to virtually generate a table that simply lists dates in one column, literally every date from '2014-01-01' to the current date (the other columns I can use a formula from there). Actually even appending future dates on for a year would probably be useful as well.



    Now yes I can grab distinct dates from another random fact table that has thousands of entries, but that seems sloppy and is creating a dependency where there really shouldn't be one.










    share|improve this question



























      2












      2








      2








      We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value.



      I'm wondering what's the simplest way to virtually generate a table that simply lists dates in one column, literally every date from '2014-01-01' to the current date (the other columns I can use a formula from there). Actually even appending future dates on for a year would probably be useful as well.



      Now yes I can grab distinct dates from another random fact table that has thousands of entries, but that seems sloppy and is creating a dependency where there really shouldn't be one.










      share|improve this question
















      We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value.



      I'm wondering what's the simplest way to virtually generate a table that simply lists dates in one column, literally every date from '2014-01-01' to the current date (the other columns I can use a formula from there). Actually even appending future dates on for a year would probably be useful as well.



      Now yes I can grab distinct dates from another random fact table that has thousands of entries, but that seems sloppy and is creating a dependency where there really shouldn't be one.







      sql-server t-sql view date






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Sep 25 '17 at 14:22









      Solomon Rutzky

      49.5k582180




      49.5k582180










      asked Jul 22 '16 at 22:07









      user45867user45867

      6192819




      6192819






















          3 Answers
          3






          active

          oldest

          votes


















          2














          Using pure T-SQL, you can do the following:



          ;WITH cte AS
          (
          SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
          FROM [master].[sys].[columns] sc1
          CROSS JOIN [master].[sys].[columns] sc2
          )
          SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
          FROM cte
          WHERE DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();


          Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:



          SELECT [DatetimeVal]
          FROM [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');


          If you want the ending date to be in the future, just replace the GETDATE() in either query.






          share|improve this answer































            0














            Try this.



            https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788



            You don't need a different table, although if you have one it could help.






            share|improve this answer

































              0














              Updated Code for Generating dates for next 20 days from today



              ;WITH cte AS
              (
              SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
              FROM [master].[sys].[columns] sc1
              CROSS JOIN [master].[sys].[columns] sc2
              )
              SELECT convert(VARCHAR,DATEADD(DAY, cte.[Incrementor], GETDATE()+2),103) as [Date]
              FROM cte
              WHERE DATEADD(DAY, cte.[Incrementor], GETDATE()-20) < GETDATE();




              share








              New contributor




              Godly Mathew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
              Check out our Code of Conduct.





















                Your Answer








                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "182"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                autoActivateHeartbeat: false,
                convertImagesToLinks: false,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: null,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f144700%2fhow-to-generate-list-of-dates-ie-every-day-for-the-past-2-years-sql-server%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                3 Answers
                3






                active

                oldest

                votes








                3 Answers
                3






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes









                2














                Using pure T-SQL, you can do the following:



                ;WITH cte AS
                (
                SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
                FROM [master].[sys].[columns] sc1
                CROSS JOIN [master].[sys].[columns] sc2
                )
                SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
                FROM cte
                WHERE DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();


                Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:



                SELECT [DatetimeVal]
                FROM [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');


                If you want the ending date to be in the future, just replace the GETDATE() in either query.






                share|improve this answer




























                  2














                  Using pure T-SQL, you can do the following:



                  ;WITH cte AS
                  (
                  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
                  FROM [master].[sys].[columns] sc1
                  CROSS JOIN [master].[sys].[columns] sc2
                  )
                  SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
                  FROM cte
                  WHERE DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();


                  Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:



                  SELECT [DatetimeVal]
                  FROM [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');


                  If you want the ending date to be in the future, just replace the GETDATE() in either query.






                  share|improve this answer


























                    2












                    2








                    2







                    Using pure T-SQL, you can do the following:



                    ;WITH cte AS
                    (
                    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
                    FROM [master].[sys].[columns] sc1
                    CROSS JOIN [master].[sys].[columns] sc2
                    )
                    SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
                    FROM cte
                    WHERE DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();


                    Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:



                    SELECT [DatetimeVal]
                    FROM [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');


                    If you want the ending date to be in the future, just replace the GETDATE() in either query.






                    share|improve this answer













                    Using pure T-SQL, you can do the following:



                    ;WITH cte AS
                    (
                    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
                    FROM [master].[sys].[columns] sc1
                    CROSS JOIN [master].[sys].[columns] sc2
                    )
                    SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
                    FROM cte
                    WHERE DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();


                    Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:



                    SELECT [DatetimeVal]
                    FROM [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');


                    If you want the ending date to be in the future, just replace the GETDATE() in either query.







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Sep 25 '17 at 14:21









                    Solomon RutzkySolomon Rutzky

                    49.5k582180




                    49.5k582180

























                        0














                        Try this.



                        https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788



                        You don't need a different table, although if you have one it could help.






                        share|improve this answer






























                          0














                          Try this.



                          https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788



                          You don't need a different table, although if you have one it could help.






                          share|improve this answer




























                            0












                            0








                            0







                            Try this.



                            https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788



                            You don't need a different table, although if you have one it could help.






                            share|improve this answer















                            Try this.



                            https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788



                            You don't need a different table, although if you have one it could help.







                            share|improve this answer














                            share|improve this answer



                            share|improve this answer








                            edited May 23 '17 at 12:40









                            Community

                            1




                            1










                            answered Jul 23 '16 at 0:29









                            Rob FarleyRob Farley

                            14k12549




                            14k12549























                                0














                                Updated Code for Generating dates for next 20 days from today



                                ;WITH cte AS
                                (
                                SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
                                FROM [master].[sys].[columns] sc1
                                CROSS JOIN [master].[sys].[columns] sc2
                                )
                                SELECT convert(VARCHAR,DATEADD(DAY, cte.[Incrementor], GETDATE()+2),103) as [Date]
                                FROM cte
                                WHERE DATEADD(DAY, cte.[Incrementor], GETDATE()-20) < GETDATE();




                                share








                                New contributor




                                Godly Mathew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                Check out our Code of Conduct.

























                                  0














                                  Updated Code for Generating dates for next 20 days from today



                                  ;WITH cte AS
                                  (
                                  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
                                  FROM [master].[sys].[columns] sc1
                                  CROSS JOIN [master].[sys].[columns] sc2
                                  )
                                  SELECT convert(VARCHAR,DATEADD(DAY, cte.[Incrementor], GETDATE()+2),103) as [Date]
                                  FROM cte
                                  WHERE DATEADD(DAY, cte.[Incrementor], GETDATE()-20) < GETDATE();




                                  share








                                  New contributor




                                  Godly Mathew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                  Check out our Code of Conduct.























                                    0












                                    0








                                    0







                                    Updated Code for Generating dates for next 20 days from today



                                    ;WITH cte AS
                                    (
                                    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
                                    FROM [master].[sys].[columns] sc1
                                    CROSS JOIN [master].[sys].[columns] sc2
                                    )
                                    SELECT convert(VARCHAR,DATEADD(DAY, cte.[Incrementor], GETDATE()+2),103) as [Date]
                                    FROM cte
                                    WHERE DATEADD(DAY, cte.[Incrementor], GETDATE()-20) < GETDATE();




                                    share








                                    New contributor




                                    Godly Mathew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.










                                    Updated Code for Generating dates for next 20 days from today



                                    ;WITH cte AS
                                    (
                                    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
                                    FROM [master].[sys].[columns] sc1
                                    CROSS JOIN [master].[sys].[columns] sc2
                                    )
                                    SELECT convert(VARCHAR,DATEADD(DAY, cte.[Incrementor], GETDATE()+2),103) as [Date]
                                    FROM cte
                                    WHERE DATEADD(DAY, cte.[Incrementor], GETDATE()-20) < GETDATE();





                                    share








                                    New contributor




                                    Godly Mathew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.








                                    share


                                    share






                                    New contributor




                                    Godly Mathew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.









                                    answered 4 mins ago









                                    Godly MathewGodly Mathew

                                    11




                                    11




                                    New contributor




                                    Godly Mathew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.





                                    New contributor





                                    Godly Mathew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.






                                    Godly Mathew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.






























                                        draft saved

                                        draft discarded




















































                                        Thanks for contributing an answer to Database Administrators Stack Exchange!


                                        • Please be sure to answer the question. Provide details and share your research!

                                        But avoid



                                        • Asking for help, clarification, or responding to other answers.

                                        • Making statements based on opinion; back them up with references or personal experience.


                                        To learn more, see our tips on writing great answers.




                                        draft saved


                                        draft discarded














                                        StackExchange.ready(
                                        function () {
                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f144700%2fhow-to-generate-list-of-dates-ie-every-day-for-the-past-2-years-sql-server%23new-answer', 'question_page');
                                        }
                                        );

                                        Post as a guest















                                        Required, but never shown





















































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown

































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown







                                        Popular posts from this blog

                                        Liste der Baudenkmale in Friedland (Mecklenburg)

                                        Single-Malt-Whisky

                                        Czorneboh