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


Date Search


Date Search

Author
Message
VegasL
VegasL
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 225
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 (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93295 Visits: 20637
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36480 Visits: 13557
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1429 Visits: 920
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36480 Visits: 13557
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1429 Visits: 920
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36480 Visits: 13557
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 (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)

Group: General Forum Members
Points: 504149 Visits: 44225
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