How to generate list of dates — ie every day for the past 2 years? (sql server) The Next CEO...
To not tell, not take, and not want
Which kind of appliances can one connect to electric sockets located in an airplane's toilet?
What does convergence in distribution "in the Gromov–Hausdorff" sense mean?
Should I tutor a student who I know has cheated on their homework?
WOW air has ceased operation, can I get my tickets refunded?
What is the result of assigning to std::vector<T>::begin()?
Unreliable Magic - Is it worth it?
What was the first Unix version to run on a microcomputer?
If the heap is initialized for security, then why is the stack uninitialized?
Is it ever safe to open a suspicious html file (e.g. email attachment)?
Is there a difference between "Fahrstuhl" and "Aufzug"
Contours of a clandestine nature
Why don't programming languages automatically manage the synchronous/asynchronous problem?
Do I need to enable Dev Hub in my PROD Org?
I believe this to be a fraud - hired, then asked to cash check and send cash as Bitcoin
Which tube will fit a -(700 x 25c) wheel?
Elegant way to replace substring in a regex with optional groups in Python?
What can we do to stop prior company from asking us questions?
Are there any limitations on attacking while grappling?
Plot of histogram similar to output from @risk
Indicator light circuit
How did the Bene Gesserit know how to make a Kwisatz Haderach?
Why didn't Khan get resurrected in the Genesis Explosion?
Why do we use the plural of movies in this phrase "We went to the movies last night."?
How to generate list of dates — ie every day for the past 2 years? (sql server)
The Next CEO of Stack OverflowGet max date for each year from list of datesResults for every hour of the day even data if not presentHow to list all past remote login informationHow to compare dates in SQL?Finding missing gaps of data in a table with ~2.5 Million rowsJoin tables where the table name changes every dayConvert list of dates in a date range in SQL ServerHow to list the table based on week or month from range of dates?How do I generate a date series in PostgreSQL without hard coding the dates?Fiscal Year to Date filter
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 2 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 2 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 2 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 2 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 2 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 2 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 2 mins ago
Godly MathewGodly Mathew
11
answered 2 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
