January 23, 2015 at 11:02 am
I have a column in the table with datatype DATE
Gives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
January 23, 2015 at 11:08 am
sharonsql2013 (1/23/2015)
I have a column in the table with datatype DATEGives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
To return the rows where date_column is later than 12 months ago:
SELECT columns_to_return
FROM dbo.XYZ
WHERE date_column > DATEADD(month, -12, GETDATE());
Note that the date math is being done on the right side of the = sign. This is so you can have some hope of performance.
January 23, 2015 at 12:00 pm
Ed Wagner (1/23/2015)
sharonsql2013 (1/23/2015)
I have a column in the table with datatype DATEGives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
To return the rows where date_column is later than 12 months ago:
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > DATEADD(month, -12, GETDATE());
Note that the date math is being done on the right side of the = sign. This is so you can have some hope of performance.
Ed you are spot on about performance however the wording makes it a little confusing. It doesn't matter what side of the equality the date math is performed. What makes this sargable is NOT having one of the columns part of the function. If you reversed the where predicate the execution plan would be 100% identical.
That is to say that the following two predicates are 100% the same thing.
WHERE date_column > DATEADD(month, -12, GETDATE());
OR
WHERE DATEADD(month, -12, GETDATE()) < date_column >
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2015 at 12:28 pm
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
sharonsql2013 (1/23/2015)
I have a column in the table with datatype DATEGives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
To return the rows where date_column is later than 12 months ago:
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > DATEADD(month, -12, GETDATE());
Note that the date math is being done on the right side of the = sign. This is so you can have some hope of performance.
Ed you are spot on about performance however the wording makes it a little confusing. It doesn't matter what side of the equality the date math is performed. What makes this sargable is NOT having one of the columns part of the function. If you reversed the where predicate the execution plan would be 100% identical.
That is to say that the following two predicates are 100% the same thing.
WHERE date_column > DATEADD(month, -12, GETDATE());
OR
WHERE DATEADD(month, -12, GETDATE()) < date_column >
Quite right - thanks for the correction. The key is to not perform the function on all rows in the table to do the comparison, only do the comparison. Thanks, Sean.
January 23, 2015 at 12:31 pm
Ed Wagner (1/23/2015)
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
sharonsql2013 (1/23/2015)
I have a column in the table with datatype DATEGives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
To return the rows where date_column is later than 12 months ago:
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > DATEADD(month, -12, GETDATE());
Note that the date math is being done on the right side of the = sign. This is so you can have some hope of performance.
Ed you are spot on about performance however the wording makes it a little confusing. It doesn't matter what side of the equality the date math is performed. What makes this sargable is NOT having one of the columns part of the function. If you reversed the where predicate the execution plan would be 100% identical.
That is to say that the following two predicates are 100% the same thing.
WHERE date_column > DATEADD(month, -12, GETDATE());
OR
WHERE DATEADD(month, -12, GETDATE()) < date_column >
Quite right - thanks for the correction. The key is to not perform the function on all rows in the table to do the comparison, only do the comparison. Thanks, Sean.
I know fully well you knew that just wanted to be clear in the answer. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2015 at 12:33 pm
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
sharonsql2013 (1/23/2015)
I have a column in the table with datatype DATEGives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
To return the rows where date_column is later than 12 months ago:
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > DATEADD(month, -12, GETDATE());
Note that the date math is being done on the right side of the = sign. This is so you can have some hope of performance.
Ed you are spot on about performance however the wording makes it a little confusing. It doesn't matter what side of the equality the date math is performed. What makes this sargable is NOT having one of the columns part of the function. If you reversed the where predicate the execution plan would be 100% identical.
That is to say that the following two predicates are 100% the same thing.
WHERE date_column > DATEADD(month, -12, GETDATE());
OR
WHERE DATEADD(month, -12, GETDATE()) < date_column >
Quite right - thanks for the correction. The key is to not perform the function on all rows in the table to do the comparison, only do the comparison. Thanks, Sean.
I know fully well you knew that just wanted to be clear in the answer. 😀
Absolutely. We don't want to lead someone down the wrong path. That's not why either of us are here.
January 23, 2015 at 1:36 pm
sharonsql2013 (1/23/2015)
I have a column in the table with datatype DATEGives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
Sharon,
What is the DATATYPE of that column? And to be clear, are the underscores included in the actual data in the column? I ask because the given solutions are great but MAY also cause non-SARGable queries due to datatype mismatches.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2015 at 1:38 pm
Ed Wagner (1/23/2015)
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
sharonsql2013 (1/23/2015)
I have a column in the table with datatype DATEGives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
To return the rows where date_column is later than 12 months ago:
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > DATEADD(month, -12, GETDATE());
Note that the date math is being done on the right side of the = sign. This is so you can have some hope of performance.
Ed you are spot on about performance however the wording makes it a little confusing. It doesn't matter what side of the equality the date math is performed. What makes this sargable is NOT having one of the columns part of the function. If you reversed the where predicate the execution plan would be 100% identical.
That is to say that the following two predicates are 100% the same thing.
WHERE date_column > DATEADD(month, -12, GETDATE());
OR
WHERE DATEADD(month, -12, GETDATE()) < date_column >
Quite right - thanks for the correction. The key is to not perform the function on all rows in the table to do the comparison, only do the comparison. Thanks, Sean.
I know fully well you knew that just wanted to be clear in the answer. 😀
Absolutely. We don't want to lead someone down the wrong path. That's not why either of us are here.
Though it works, I find it easier to read and seems more logical when the column is on the left side of the "=" and the search criteria is on the right.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2015 at 1:48 pm
Jeff Moden (1/23/2015)
Ed Wagner (1/23/2015)
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
sharonsql2013 (1/23/2015)
I have a column in the table with datatype DATEGives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
To return the rows where date_column is later than 12 months ago:
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > DATEADD(month, -12, GETDATE());
Note that the date math is being done on the right side of the = sign. This is so you can have some hope of performance.
Ed you are spot on about performance however the wording makes it a little confusing. It doesn't matter what side of the equality the date math is performed. What makes this sargable is NOT having one of the columns part of the function. If you reversed the where predicate the execution plan would be 100% identical.
That is to say that the following two predicates are 100% the same thing.
WHERE date_column > DATEADD(month, -12, GETDATE());
OR
WHERE DATEADD(month, -12, GETDATE()) < date_column >
Quite right - thanks for the correction. The key is to not perform the function on all rows in the table to do the comparison, only do the comparison. Thanks, Sean.
I know fully well you knew that just wanted to be clear in the answer. 😀
Absolutely. We don't want to lead someone down the wrong path. That's not why either of us are here.
Though it works, I find it easier to read and seems more logical when the column is on the left side of the "=" and the search criteria is on the right.
Agreed I find it rather obtuse when they are "backwards".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 26, 2015 at 5:39 am
Sean Lange (1/23/2015)
Jeff Moden (1/23/2015)
Ed Wagner (1/23/2015)
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
sharonsql2013 (1/23/2015)
I have a column in the table with datatype DATEGives data in the format
YYYY_MM_DD
How can I go back to past 12 months in my where clause
So , Select * from XYZ
where Date_YYYY_MM_DD ......
To return the rows where date_column is later than 12 months ago:
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > DATEADD(month, -12, GETDATE());
Note that the date math is being done on the right side of the = sign. This is so you can have some hope of performance.
Ed you are spot on about performance however the wording makes it a little confusing. It doesn't matter what side of the equality the date math is performed. What makes this sargable is NOT having one of the columns part of the function. If you reversed the where predicate the execution plan would be 100% identical.
That is to say that the following two predicates are 100% the same thing.
WHERE date_column > DATEADD(month, -12, GETDATE());
OR
WHERE DATEADD(month, -12, GETDATE()) < date_column >
Quite right - thanks for the correction. The key is to not perform the function on all rows in the table to do the comparison, only do the comparison. Thanks, Sean.
I know fully well you knew that just wanted to be clear in the answer. 😀
Absolutely. We don't want to lead someone down the wrong path. That's not why either of us are here.
Though it works, I find it easier to read and seems more logical when the column is on the left side of the "=" and the search criteria is on the right.
Agreed I find it rather obtuse when they are "backwards".
Agreed completely. I see enough code that's hard to read without things being bassackwards.
January 27, 2015 at 12:08 pm
I too like the column on the left and expression on the right. I also like to use the year argument to know I am interested in one year back, when I look at this later. Of course it is not necessary as months works just as well, just a personal choice.
WHERE date_yyyy_mm_dd >= dateadd(yyyy, -1, CAST(getdate() as date))
The equal sign will include the (today's date a year ago)... if you want to start at the day after that, just remove the equal sign.
----------------------------------------------------
January 27, 2015 at 12:59 pm
If it's a string column with underscores between each part of the date. This could be an option.
SELECT columns_to_return
FROM dbo.XYZ
WHERE date_column > REPLACE( CONVERT( char(10), DATEADD(YY, -1, GETDATE()), 120), '-', '_');
This is SARGable, but using strings to store dates is just unnecesary pain, IMHO. And I speak from experience.
January 27, 2015 at 1:10 pm
Luis Cazares (1/27/2015)
If it's a string column with underscores between each part of the date. This could be an option.
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > REPLACE( CONVERT( char(10), DATEADD(YY, -1, GETDATE()), 120), '-', '_');
This is SARGable, but using strings to store dates is just unnecesary pain, IMHO. And I speak from experience.
Just as bad if not worse, I have seen tables that separate the date and the time, and stored them as strings. Ohh boy was that a pain to work with.
----------------------------------------------------
January 27, 2015 at 1:21 pm
MMartin1 (1/27/2015)
Luis Cazares (1/27/2015)
If it's a string column with underscores between each part of the date. This could be an option.
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > REPLACE( CONVERT( char(10), DATEADD(YY, -1, GETDATE()), 120), '-', '_');
This is SARGable, but using strings to store dates is just unnecesary pain, IMHO. And I speak from experience.
Just as bad if not worse, I have seen tables that separate the date and the time, and stored them as strings. Ohh boy was that a pain to work with.
What about the ones that have separate year, month, day columns? :crazy: :sick:
January 27, 2015 at 1:56 pm
Luis Cazares (1/27/2015)
MMartin1 (1/27/2015)
Luis Cazares (1/27/2015)
If it's a string column with underscores between each part of the date. This could be an option.
SELECT columns_to_returnFROM dbo.XYZ
WHERE date_column > REPLACE( CONVERT( char(10), DATEADD(YY, -1, GETDATE()), 120), '-', '_');
This is SARGable, but using strings to store dates is just unnecesary pain, IMHO. And I speak from experience.
Just as bad if not worse, I have seen tables that separate the date and the time, and stored them as strings. Ohh boy was that a pain to work with.
What about the ones that have separate year, month, day columns? :crazy: :sick:
Oh gee that's no problem. Just create a computed column that concatenates all those asinine strings back into a datetime so you can use it. :crying:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply