SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Search


Date Search

Author
Message
VegasL
VegasL
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2248 Visits: 378
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" ?
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)

Group: General Forum Members
Points: 156739 Visits: 23028
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)

Cool

drew.allen
drew.allen
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62293 Visits: 16619
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)

Cool

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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
gvoshol 73146
gvoshol 73146
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2418 Visits: 1257
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
drew.allen
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62293 Visits: 16619
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
gvoshol 73146
gvoshol 73146
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2418 Visits: 1257
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
drew.allen
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62293 Visits: 16619
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859563 Visits: 47093
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search