Date Search

Question

Date Search

VegasL

Hall of Fame

Points: 3006
More actions
November 11, 2017 at 10:44 pm

#409767

Hi,
I have in a data row: column [date] 2017-11-06 01:17:34.000
I only want to search for values if the year is 2015. How do I do this?
where [date] like '%2015" ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

VegasL - Saturday, November 11, 2017 10:44 PM
Hi,
I have in a data row: column [date] 2017-11-06 01:17:34.000
I only want to search for values if the year is 2015. How do I do this?
where [date] like '%2015" ?

This is a datetime value, neither a string nor date value, constrain the search accordingly, i.e.
WHERE [date] > CONVERT(DATEDIME,'2014-12-31 23:59',126) AND [date] < CONVERT(DATEDIME,'2016-01-01 00:00',126)
😎

drew.allen SSC Guru Points: 76944 More actions · Answer 2

Eirikur Eiriksson - Sunday, November 12, 2017 2:57 AM
VegasL - Saturday, November 11, 2017 10:44 PM
Hi,
I have in a data row: column [date] 2017-11-06 01:17:34.000
I only want to search for values if the year is 2015. How do I do this?
where [date] like '%2015" ?
This is a datetime value, neither a string nor date value, constrain the search accordingly, i.e.
WHERE [date] > CONVERT(DATEDIME,'2014-12-31 23:59',126) AND [date] < CONVERT(DATEDIME,'2016-01-01 00:00',126)
😎

This will include records that you don't want to include. When using date ranges, you should be using half-closed intervals. Most people use closed intervals, but this goes to the other extreme and uses open intervals. Neither is correct for most uses. Specifically, this method will include dates between 2014-12-31 23:59:00.003 and 2014-12-31 23:59:59.997 that you do not want to include. The correct formulation is as follows.

WHERE [date] >= CAST('2015-01-01' AS DATETIME) AND [date] < CAST('2016-01-01' AS DATETIME)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

GaryV Hall of Fame Points: 3307 More actions · Answer 3

Of course the absolute easiest way of doing it is
WHERE YEAR([date]) = 2015
But that's not sargable. It doesn't matter if you're looking at only a few thousand rows in your table. But if your table has hundreds of thousands or millions of rows, then performance will suffer greatly.

drew.allen SSC Guru Points: 76944 More actions · Answer 4

gvoshol 73146 - Tuesday, November 14, 2017 5:35 AM
Of course the absolute easiest way of doing it is
WHERE YEAR([date]) = 2015
But that's not sargable. It doesn't matter if you're looking at only a few thousand rows in your table. But if your table has hundreds of thousands or millions of rows, then performance will suffer greatly.

I would argue that it does matter. There is an adage that "If you fail to plan, you plan to fail." This is very applicable to this situation, because using the SARGable method plans for growth, whereas the non-SARGable method does not.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

GaryV Hall of Fame Points: 3307 More actions · Answer 5

drew.allen - Tuesday, November 14, 2017 7:50 AM
gvoshol 73146 - Tuesday, November 14, 2017 5:35 AM
Of course the absolute easiest way of doing it is
WHERE YEAR([date]) = 2015
But that's not sargable. It doesn't matter if you're looking at only a few thousand rows in your table. But if your table has hundreds of thousands or millions of rows, then performance will suffer greatly.
I would argue that it does matter. There is an adage that "If you fail to plan, you plan to fail." This is very applicable to this situation, because using the SARGable method plans for growth, whereas the non-SARGable method does not.
Drew

I should have been more precise in my wording.
"If you have a lower number of records, and you KNOW the number of records will not be increasing significantly, then you can use a non-Sargable function."

drew.allen SSC Guru Points: 76944 More actions · Answer 6

gvoshol 73146 - Tuesday, November 14, 2017 8:08 AM
drew.allen - Tuesday, November 14, 2017 7:50 AM
gvoshol 73146 - Tuesday, November 14, 2017 5:35 AM
Of course the absolute easiest way of doing it is
WHERE YEAR([date]) = 2015
But that's not sargable. It doesn't matter if you're looking at only a few thousand rows in your table. But if your table has hundreds of thousands or millions of rows, then performance will suffer greatly.
I would argue that it does matter. There is an adage that "If you fail to plan, you plan to fail." This is very applicable to this situation, because using the SARGable method plans for growth, whereas the non-SARGable method does not.
Drew
I should have been more precise in my wording.
"If you have a lower number of records, and you KNOW the number of records will not be increasing significantly, then you can use a non-Sargable function."

You may be aware of the trade offs and willing to sacrifice performance in a given situation, but another person may not and may come along and copy your code where the performance loss is not acceptable.

Also, it's highly unlikely that, when working with dates, the number of records will not be increasing significantly.

Laziness is not an acceptable excuse for sacrificing performance.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

drew.allen - Tuesday, November 14, 2017 8:30 AM
gvoshol 73146 - Tuesday, November 14, 2017 8:08 AM
drew.allen - Tuesday, November 14, 2017 7:50 AM
gvoshol 73146 - Tuesday, November 14, 2017 5:35 AM
Of course the absolute easiest way of doing it is
WHERE YEAR([date]) = 2015
But that's not sargable. It doesn't matter if you're looking at only a few thousand rows in your table. But if your table has hundreds of thousands or millions of rows, then performance will suffer greatly.
I would argue that it does matter. There is an adage that "If you fail to plan, you plan to fail." This is very applicable to this situation, because using the SARGable method plans for growth, whereas the non-SARGable method does not.
Drew
I should have been more precise in my wording.
"If you have a lower number of records, and you KNOW the number of records will not be increasing significantly, then you can use a non-Sargable function."
You may be aware of the trade offs and willing to sacrifice performance in a given situation, but another person may not and may come along and copy your code where the performance loss is not acceptable.
Also, it's highly unlikely that, when working with dates, the number of records will not be increasing significantly.
Laziness is not an acceptable excuse for sacrificing performance.
Drew

+1000000 to that!!! If you "practice" doing it the wrong way, you'll never get good at doing it the right way.

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