DAte Function

Question

DAte Function

sharonsql2013

SSCertifiable

Points: 5908
More actions
January 23, 2015 at 11:02 am

#302505

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 ......

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

sharonsql2013 (1/23/2015)
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 ......

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Ed Wagner (1/23/2015)
sharonsql2013 (1/23/2015)
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 ......
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.

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/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Sean Lange (1/23/2015)
Ed Wagner (1/23/2015)
sharonsql2013 (1/23/2015)
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 ......
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.
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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

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 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 ......
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.
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/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

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 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 ......
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.
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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

sharonsql2013 (1/23/2015)
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 ......

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

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 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 ......
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.
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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

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 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 ......
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.
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/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

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 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 ......
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.
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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

MMartin1 One Orange Chip Points: 27795 More actions · Answer 10

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.

----------------------------------------------------

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

MMartin1 One Orange Chip Points: 27795 More actions · Answer 12

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_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.

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.

----------------------------------------------------

Luis Cazares SSC Guru Points: 183706 More actions · Answer 13

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_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.
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:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

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_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.
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/