How to generate list of dates — ie every day for the past 2 years? (sql server)
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
edited Sep 25 '17 at 14:22
Solomon Rutzky
49.5k582180
asked Jul 22 '16 at 22:07
user45867user45867
6192819
add a comment |
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
edited Sep 25 '17 at 14:22
Solomon Rutzky
49.5k582180
asked Jul 22 '16 at 22:07
user45867user45867
6192819
add a comment |
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
edited Sep 25 '17 at 14:22
Solomon Rutzky
49.5k582180
asked Jul 22 '16 at 22:07
user45867user45867
6192819
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
sql-server t-sql view date
edited Sep 25 '17 at 14:22
Solomon Rutzky
49.5k582180
asked Jul 22 '16 at 22:07
user45867user45867
6192819
edited Sep 25 '17 at 14:22
Solomon Rutzky
49.5k582180
asked Jul 22 '16 at 22:07
user45867user45867
6192819
edited Sep 25 '17 at 14:22
Solomon Rutzky
49.5k582180
edited Sep 25 '17 at 14:22
Solomon Rutzky
49.5k582180
edited Sep 25 '17 at 14:22
Solomon Rutzky
49.5k582180
49.5k582180
asked Jul 22 '16 at 22:07
user45867user45867
6192819
asked Jul 22 '16 at 22:07
user45867user45867
6192819
asked Jul 22 '16 at 22:07
user45867user45867
6192819
6192819
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
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.
answered Sep 25 '17 at 14:21
Solomon RutzkySolomon Rutzky
49.5k582180
add a comment |
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.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 23 '16 at 0:29
Rob FarleyRob Farley
14k12549
add a comment |
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();
answered 4 mins ago
Godly MathewGodly Mathew
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.
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered Sep 25 '17 at 14:21
Solomon RutzkySolomon Rutzky
49.5k582180
add a comment |
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.
answered Sep 25 '17 at 14:21
Solomon RutzkySolomon Rutzky
49.5k582180
add a comment |
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.
answered Sep 25 '17 at 14:21
Solomon RutzkySolomon Rutzky
49.5k582180
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.
answered Sep 25 '17 at 14:21
Solomon RutzkySolomon Rutzky
49.5k582180
answered Sep 25 '17 at 14:21
Solomon RutzkySolomon Rutzky
49.5k582180
answered Sep 25 '17 at 14:21
Solomon RutzkySolomon Rutzky
49.5k582180
answered Sep 25 '17 at 14:21
Solomon RutzkySolomon Rutzky
49.5k582180
49.5k582180
add a comment |
add a comment |
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.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 23 '16 at 0:29
Rob FarleyRob Farley
14k12549
add a comment |
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.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 23 '16 at 0:29
Rob FarleyRob Farley
14k12549
add a comment |
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.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 23 '16 at 0:29
Rob FarleyRob Farley
14k12549
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.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 23 '16 at 0:29
Rob FarleyRob Farley
14k12549
edited May 23 '17 at 12:40
Community♦
1
edited May 23 '17 at 12:40
Community♦
1
edited May 23 '17 at 12:40
Community♦
1
1
answered Jul 23 '16 at 0:29
Rob FarleyRob Farley
14k12549
answered Jul 23 '16 at 0:29
Rob FarleyRob Farley
14k12549
answered Jul 23 '16 at 0:29
Rob FarleyRob Farley
14k12549
14k12549
add a comment |
add a comment |
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();
answered 4 mins ago
Godly MathewGodly Mathew
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.
add a comment |
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();
answered 4 mins ago
Godly MathewGodly Mathew
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.
add a comment |
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();
answered 4 mins ago
Godly MathewGodly Mathew
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.
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();
answered 4 mins ago
Godly MathewGodly Mathew
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.
answered 4 mins ago
Godly MathewGodly Mathew
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.
answered 4 mins ago
Godly MathewGodly Mathew
11
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.
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
