Getting SQL server to recognise a date column
I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed.
My table looks like this
orders (
order_id INT PRIMARY KEY
, user_id INT
, date_created DATE
, order_value FLOAT
, city_id INT
)
I have used this code to get todays date in the table -
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
This works fine but when I try attempt a where clause below this
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
where Today -1 = date_created;
I receive this error "Invalid column name 'Today'"
So my question is simply how can I get this table to recognise the the date I added as a new column and allow me to perform work on it?
Thanks in advance
sql-server date alias
add a comment |
I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed.
My table looks like this
orders (
order_id INT PRIMARY KEY
, user_id INT
, date_created DATE
, order_value FLOAT
, city_id INT
)
I have used this code to get todays date in the table -
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
This works fine but when I try attempt a where clause below this
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
where Today -1 = date_created;
I receive this error "Invalid column name 'Today'"
So my question is simply how can I get this table to recognise the the date I added as a new column and allow me to perform work on it?
Thanks in advance
sql-server date alias
1
Dan shows a much smoother way to convertGETDATE()
(well, any date, really) to date:CONVERT(date, GETDATE())
. Also I strongly recommend avoiding shorthand (you typedd
; why not just typeday
?) andToday-1
(this will break with newer date/time types). See this and this.
– Aaron Bertrand♦
Dec 1 at 14:11
Thanks for the links!! Dans answer does work perfectly but I wanted a count and just that and with Dans method I have two column headings, one with the count and the other todays date. I thought this might be confusing for someone who wasnt familar with the script. I am studying both answers as I am sure will be needed again in future.
– RustyRyan
Dec 1 at 14:38
No worries, I wasn't trying to suggest one answer over another, just acknowledging that Dan also mentioned convert to date vs. dateadd/datediff hackiness.
– Aaron Bertrand♦
Dec 1 at 15:16
add a comment |
I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed.
My table looks like this
orders (
order_id INT PRIMARY KEY
, user_id INT
, date_created DATE
, order_value FLOAT
, city_id INT
)
I have used this code to get todays date in the table -
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
This works fine but when I try attempt a where clause below this
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
where Today -1 = date_created;
I receive this error "Invalid column name 'Today'"
So my question is simply how can I get this table to recognise the the date I added as a new column and allow me to perform work on it?
Thanks in advance
sql-server date alias
I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed.
My table looks like this
orders (
order_id INT PRIMARY KEY
, user_id INT
, date_created DATE
, order_value FLOAT
, city_id INT
)
I have used this code to get todays date in the table -
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
This works fine but when I try attempt a where clause below this
Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
where Today -1 = date_created;
I receive this error "Invalid column name 'Today'"
So my question is simply how can I get this table to recognise the the date I added as a new column and allow me to perform work on it?
Thanks in advance
sql-server date alias
sql-server date alias
edited Dec 1 at 15:17
Paul White♦
48.8k14259414
48.8k14259414
asked Dec 1 at 13:12
RustyRyan
474
474
1
Dan shows a much smoother way to convertGETDATE()
(well, any date, really) to date:CONVERT(date, GETDATE())
. Also I strongly recommend avoiding shorthand (you typedd
; why not just typeday
?) andToday-1
(this will break with newer date/time types). See this and this.
– Aaron Bertrand♦
Dec 1 at 14:11
Thanks for the links!! Dans answer does work perfectly but I wanted a count and just that and with Dans method I have two column headings, one with the count and the other todays date. I thought this might be confusing for someone who wasnt familar with the script. I am studying both answers as I am sure will be needed again in future.
– RustyRyan
Dec 1 at 14:38
No worries, I wasn't trying to suggest one answer over another, just acknowledging that Dan also mentioned convert to date vs. dateadd/datediff hackiness.
– Aaron Bertrand♦
Dec 1 at 15:16
add a comment |
1
Dan shows a much smoother way to convertGETDATE()
(well, any date, really) to date:CONVERT(date, GETDATE())
. Also I strongly recommend avoiding shorthand (you typedd
; why not just typeday
?) andToday-1
(this will break with newer date/time types). See this and this.
– Aaron Bertrand♦
Dec 1 at 14:11
Thanks for the links!! Dans answer does work perfectly but I wanted a count and just that and with Dans method I have two column headings, one with the count and the other todays date. I thought this might be confusing for someone who wasnt familar with the script. I am studying both answers as I am sure will be needed again in future.
– RustyRyan
Dec 1 at 14:38
No worries, I wasn't trying to suggest one answer over another, just acknowledging that Dan also mentioned convert to date vs. dateadd/datediff hackiness.
– Aaron Bertrand♦
Dec 1 at 15:16
1
1
Dan shows a much smoother way to convert
GETDATE()
(well, any date, really) to date: CONVERT(date, GETDATE())
. Also I strongly recommend avoiding shorthand (you type dd
; why not just type day
?) and Today-1
(this will break with newer date/time types). See this and this.– Aaron Bertrand♦
Dec 1 at 14:11
Dan shows a much smoother way to convert
GETDATE()
(well, any date, really) to date: CONVERT(date, GETDATE())
. Also I strongly recommend avoiding shorthand (you type dd
; why not just type day
?) and Today-1
(this will break with newer date/time types). See this and this.– Aaron Bertrand♦
Dec 1 at 14:11
Thanks for the links!! Dans answer does work perfectly but I wanted a count and just that and with Dans method I have two column headings, one with the count and the other todays date. I thought this might be confusing for someone who wasnt familar with the script. I am studying both answers as I am sure will be needed again in future.
– RustyRyan
Dec 1 at 14:38
Thanks for the links!! Dans answer does work perfectly but I wanted a count and just that and with Dans method I have two column headings, one with the count and the other todays date. I thought this might be confusing for someone who wasnt familar with the script. I am studying both answers as I am sure will be needed again in future.
– RustyRyan
Dec 1 at 14:38
No worries, I wasn't trying to suggest one answer over another, just acknowledging that Dan also mentioned convert to date vs. dateadd/datediff hackiness.
– Aaron Bertrand♦
Dec 1 at 15:16
No worries, I wasn't trying to suggest one answer over another, just acknowledging that Dan also mentioned convert to date vs. dateadd/datediff hackiness.
– Aaron Bertrand♦
Dec 1 at 15:16
add a comment |
3 Answers
3
active
oldest
votes
Your query doesn't work because of the way queries are logically processed.
You need to either use a common table expression:
WITH c
AS
(
SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders
)
SELECT *
FROM c
WHERE c.Today - 1 = date_created;
Or you could use a derived table.
SELECT *
FROM ( SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders ) AS c
WHERE c.Today - 1 = date_created;
Neither one is "better".
add a comment |
I receive this error "Invalid column name 'Today'"
You can't reference the Today
expression in the WHERE
clause because the WHERE
clause is evaluated before the SELECT
clause according to the logical order of query processing.
Since your desired type is date
, it would be better to use CAST
or CONVERT
rather than the DATEADD...DATEDIFF
ugliness (common before SQL Server 2008 introduced the date
datatype). Also, you need to use DATEADD
to calculate yesterday's date instead of a subtraction operator.
The example below uses these techniques to get the orders from yesterday:
SELECT
order_id
, user_id
, date_created
, order_value
, city_id
, CAST(GETDATE() AS date) as Today
FROM dbo.orders
WHERE DATEADD(day, -1, CAST(GETDATE() AS date)) = date_created;
add a comment |
As others have indicated you're not able to use a calculation done in the SELECT clause in the WHERE clause due to the order of operations.
However, a nifty workaround I use from time to time is to do some calculations within a CROSS APPLY, and then those can be referenced in SELECT and WHERE. It is a helpful technique to avoid repeating code.
SELECT
A.*,
S1.Today
FROM orders A
CROSS APPLY
(
SELECT
CAST(GETDATE() AS DATE) Today
) S1
WHERE DATEADD(DAY, -1, S1.Today) = A.date_created;
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%2f223906%2fgetting-sql-server-to-recognise-a-date-column%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
Your query doesn't work because of the way queries are logically processed.
You need to either use a common table expression:
WITH c
AS
(
SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders
)
SELECT *
FROM c
WHERE c.Today - 1 = date_created;
Or you could use a derived table.
SELECT *
FROM ( SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders ) AS c
WHERE c.Today - 1 = date_created;
Neither one is "better".
add a comment |
Your query doesn't work because of the way queries are logically processed.
You need to either use a common table expression:
WITH c
AS
(
SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders
)
SELECT *
FROM c
WHERE c.Today - 1 = date_created;
Or you could use a derived table.
SELECT *
FROM ( SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders ) AS c
WHERE c.Today - 1 = date_created;
Neither one is "better".
add a comment |
Your query doesn't work because of the way queries are logically processed.
You need to either use a common table expression:
WITH c
AS
(
SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders
)
SELECT *
FROM c
WHERE c.Today - 1 = date_created;
Or you could use a derived table.
SELECT *
FROM ( SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders ) AS c
WHERE c.Today - 1 = date_created;
Neither one is "better".
Your query doesn't work because of the way queries are logically processed.
You need to either use a common table expression:
WITH c
AS
(
SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders
)
SELECT *
FROM c
WHERE c.Today - 1 = date_created;
Or you could use a derived table.
SELECT *
FROM ( SELECT *, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS Today
FROM orders ) AS c
WHERE c.Today - 1 = date_created;
Neither one is "better".
edited Dec 1 at 14:32
answered Dec 1 at 13:33
sp_BlitzErik
20.7k1262102
20.7k1262102
add a comment |
add a comment |
I receive this error "Invalid column name 'Today'"
You can't reference the Today
expression in the WHERE
clause because the WHERE
clause is evaluated before the SELECT
clause according to the logical order of query processing.
Since your desired type is date
, it would be better to use CAST
or CONVERT
rather than the DATEADD...DATEDIFF
ugliness (common before SQL Server 2008 introduced the date
datatype). Also, you need to use DATEADD
to calculate yesterday's date instead of a subtraction operator.
The example below uses these techniques to get the orders from yesterday:
SELECT
order_id
, user_id
, date_created
, order_value
, city_id
, CAST(GETDATE() AS date) as Today
FROM dbo.orders
WHERE DATEADD(day, -1, CAST(GETDATE() AS date)) = date_created;
add a comment |
I receive this error "Invalid column name 'Today'"
You can't reference the Today
expression in the WHERE
clause because the WHERE
clause is evaluated before the SELECT
clause according to the logical order of query processing.
Since your desired type is date
, it would be better to use CAST
or CONVERT
rather than the DATEADD...DATEDIFF
ugliness (common before SQL Server 2008 introduced the date
datatype). Also, you need to use DATEADD
to calculate yesterday's date instead of a subtraction operator.
The example below uses these techniques to get the orders from yesterday:
SELECT
order_id
, user_id
, date_created
, order_value
, city_id
, CAST(GETDATE() AS date) as Today
FROM dbo.orders
WHERE DATEADD(day, -1, CAST(GETDATE() AS date)) = date_created;
add a comment |
I receive this error "Invalid column name 'Today'"
You can't reference the Today
expression in the WHERE
clause because the WHERE
clause is evaluated before the SELECT
clause according to the logical order of query processing.
Since your desired type is date
, it would be better to use CAST
or CONVERT
rather than the DATEADD...DATEDIFF
ugliness (common before SQL Server 2008 introduced the date
datatype). Also, you need to use DATEADD
to calculate yesterday's date instead of a subtraction operator.
The example below uses these techniques to get the orders from yesterday:
SELECT
order_id
, user_id
, date_created
, order_value
, city_id
, CAST(GETDATE() AS date) as Today
FROM dbo.orders
WHERE DATEADD(day, -1, CAST(GETDATE() AS date)) = date_created;
I receive this error "Invalid column name 'Today'"
You can't reference the Today
expression in the WHERE
clause because the WHERE
clause is evaluated before the SELECT
clause according to the logical order of query processing.
Since your desired type is date
, it would be better to use CAST
or CONVERT
rather than the DATEADD...DATEDIFF
ugliness (common before SQL Server 2008 introduced the date
datatype). Also, you need to use DATEADD
to calculate yesterday's date instead of a subtraction operator.
The example below uses these techniques to get the orders from yesterday:
SELECT
order_id
, user_id
, date_created
, order_value
, city_id
, CAST(GETDATE() AS date) as Today
FROM dbo.orders
WHERE DATEADD(day, -1, CAST(GETDATE() AS date)) = date_created;
answered Dec 1 at 13:33
Dan Guzman
13.4k11734
13.4k11734
add a comment |
add a comment |
As others have indicated you're not able to use a calculation done in the SELECT clause in the WHERE clause due to the order of operations.
However, a nifty workaround I use from time to time is to do some calculations within a CROSS APPLY, and then those can be referenced in SELECT and WHERE. It is a helpful technique to avoid repeating code.
SELECT
A.*,
S1.Today
FROM orders A
CROSS APPLY
(
SELECT
CAST(GETDATE() AS DATE) Today
) S1
WHERE DATEADD(DAY, -1, S1.Today) = A.date_created;
add a comment |
As others have indicated you're not able to use a calculation done in the SELECT clause in the WHERE clause due to the order of operations.
However, a nifty workaround I use from time to time is to do some calculations within a CROSS APPLY, and then those can be referenced in SELECT and WHERE. It is a helpful technique to avoid repeating code.
SELECT
A.*,
S1.Today
FROM orders A
CROSS APPLY
(
SELECT
CAST(GETDATE() AS DATE) Today
) S1
WHERE DATEADD(DAY, -1, S1.Today) = A.date_created;
add a comment |
As others have indicated you're not able to use a calculation done in the SELECT clause in the WHERE clause due to the order of operations.
However, a nifty workaround I use from time to time is to do some calculations within a CROSS APPLY, and then those can be referenced in SELECT and WHERE. It is a helpful technique to avoid repeating code.
SELECT
A.*,
S1.Today
FROM orders A
CROSS APPLY
(
SELECT
CAST(GETDATE() AS DATE) Today
) S1
WHERE DATEADD(DAY, -1, S1.Today) = A.date_created;
As others have indicated you're not able to use a calculation done in the SELECT clause in the WHERE clause due to the order of operations.
However, a nifty workaround I use from time to time is to do some calculations within a CROSS APPLY, and then those can be referenced in SELECT and WHERE. It is a helpful technique to avoid repeating code.
SELECT
A.*,
S1.Today
FROM orders A
CROSS APPLY
(
SELECT
CAST(GETDATE() AS DATE) Today
) S1
WHERE DATEADD(DAY, -1, S1.Today) = A.date_created;
answered Dec 1 at 20:58
Eilert Hjelmeseth
1864
1864
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f223906%2fgetting-sql-server-to-recognise-a-date-column%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
1
Dan shows a much smoother way to convert
GETDATE()
(well, any date, really) to date:CONVERT(date, GETDATE())
. Also I strongly recommend avoiding shorthand (you typedd
; why not just typeday
?) andToday-1
(this will break with newer date/time types). See this and this.– Aaron Bertrand♦
Dec 1 at 14:11
Thanks for the links!! Dans answer does work perfectly but I wanted a count and just that and with Dans method I have two column headings, one with the count and the other todays date. I thought this might be confusing for someone who wasnt familar with the script. I am studying both answers as I am sure will be needed again in future.
– RustyRyan
Dec 1 at 14:38
No worries, I wasn't trying to suggest one answer over another, just acknowledging that Dan also mentioned convert to date vs. dateadd/datediff hackiness.
– Aaron Bertrand♦
Dec 1 at 15:16